Create trigger to write to text file

[stapet]

Hello to all,

I would like to create a trigger that writes records to a text file after every insert. I am currently inserting records to a table through a Stored Procedure, but I would also like the record copied to a text file. Could anyone point me in the right direction on finding the syntax or information on writing to a text file through SQL Server?? Thanks a bunch in advance.

 

[ClaireHCM]

You can use the same stored procedure to do both functionalities

The testa table here has two columns

ALTER proc usp_insert (@id int,@name varchar(30))
as
insert into testa select @id,@name
exec master..xp_cmdshell 'osql -S(local) -dclaire -Q"select * from testa" -E >c:\test.txt'

 

[stapet]

Could you please explain the following part of the command:
'osql -S(local) -dclaire -Q"select @ from testa" -E >C:\text.txt'

I do understand the "select @ from testa".

I have tried to play with this command(in the Query Analyzer), but I can't get anything to show up in my text file. I am trying to get the word 'hello' to show up.
The following code is what I have been trying:

DECLARE @text varchar(10)
DECLARE @file varchar(100)
DECLARE @query varchar(255)

SET @file = 'C:/My Documents/dp/Desktop/MyTest.txt'
SET @text = ' echo '+ ' hello '
SET @query = @text + ' > '+ @file
EXEC master..xp_cmdshell @query