MSSQL 操作TXT文件

在存储过程或触发器里执行操作系统下的TXT文件, 有时用做系统交互时挺好的(个人意见) :)

if object_id('#a') is not null drop table #a
go
create table #a(line nvarchar(1000))
go
if object_id('aaa') is not null drop table aaa
go
create table aaa(row int identity primary key,
    version varchar(20) default cast(getdate() as varchar(20)))
go
if object_id('updateCache') is not null drop trigger updateCache
go
CREATE TRIGGER UpdateCache
ON aaa
FOR UPDATE, DELETE, INSERT
AS
DECLARE @cmd Varchar( 200 )
SELECT @cmd = 'echo ' + Cast(newid() as varchar(40) ) +
  ' > c:\leChange.txt'
EXEC master..xp_cmdshell @cmd, no_output
print 'please check the command  ---->'+@cmd
SELECT @cmd = 'type c:\leChange.txt'
delete #a
insert into #a EXEC master..xp_cmdshell @cmd
select left(line,70) as [文件内容] from #a
go
print 'test 1'
insert aaa default values
select * from aaa
print 'test 2'
update aaa set version=dateadd(month,1,version)
select * from aaa
print 'test 3'
delete aaa
select * from aaa
go