I have an issue where I am trying to create a trigger that will output a single row to a text file on insert, based on a field in that insert. rowID is a primary key that increments itself, triggerfield is user entered data. If I create this trigger and then try to insert, the inset statement hangs and I have to kill and restart the instance. Does any one know if this has to do with record locks or if there is an easier way to accomplish exporting a single row on insert?
This is the trigger I have right now--
CREATE TRIGGER [dbo].[T_OUTPUT_FILE]
IF (SELECT COUNT(*) FROM inserted WHERE [TRIGGERFIELD] = 5) = 1
SET NOCOUNT ON;
DECLARE @FileName varchar(50),
SET @Record = (Select rowID from inserted WHERE [TRIGGERFIELD] = 5)
SET @FileName = REPLACE('c:\Testing\DropFolder\row_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM database.dbo.table WHERE rowID = convert(int,'
SET @bcpCommand = @bcpCommand + @Record + ')" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U Username -P Password -c -S Server\Instance'
exec master..xp_cmdshell @bcpCommand