When a table is modified, I want a trigger to export the data of table to a file,such as *.txt or other.
I have tried to realize that by using following codes, but failed.
create trigger tr_ActionLog on ActionLog for update,insert, delete
AS
SET NOCOUNT ON
–declare @strSql varchar(255)
exec dbo.ExportData2Csv ‘liulzh’, ‘TCDB’, ‘ActionLog’, ‘sa’, ”, ‘e:\ActionLog.csv’, ‘,’
create procedure ExportData2Csv
@serverName varchar(255),
@dbName varchar(100),
@tblName varchar(100), –name of the table to be export
@username varchar(100),
@password varchar(100),
@csvFilename varchar(255), –the abosolute cvs file nmae
@splitChar char –the char to split the data of different column
AS
SET NOCOUNT ON
declare @err int, @strSql varchar(255);
set @strSql = ‘bcp ‘ + @dbName + ‘..’ + @tblName + ‘ out ‘ + @csvFilename
+ ‘ -c -S ‘ + @serverName + ‘ -t ‘ + @splitChar + ‘ -U ‘ + @username + ‘ -P ‘ + @password
exec master..xp_cmdshell @strSql
Pls help me as soon as possible, thank you very much!
