I have a trigger to detect when a certain column is updated to become a certain value. When triggered, I need to run an external program.
A sequence like:
CREATE TRIGGER TR_Inquiry_u
IF UPDATE( nInqStatus ) BEGIN
IF ( SELECT nInqStatus FROM inserted ) = 7 /* QS_Rcvd*/ BEGIN
DECLARE @sCmd varchar(256)
SET @sCmd= 'echo>c:\temp\test.txt HI there!'
EXEC master..xp_cmdshell @sCmd, no_output
...works just fine, but I don't need to run the echo command. I need to run a program that will open and access the database and it must access the trigger table. That table is locked -- presumably because the trigger is still active.
Is the xp_cmdshell the right way to run an external program? Is there a better way?
Is there a way to make the external command run asynchronously, or to end the trigger's lock, or something -- so that the external program can access the trigger table? If so, please describe in detail.