Trigger and BCP

Working on a cludgy interface between two software vendors, I will call them Vendor A and Vendor B. Vendor B wanted a CSV file every time a certain condition happened in a Vendor A table. First thought was to write a trigger on the table in Vendor A’s database. After Insert run a query to see if the condition is met…if the condition is met then send data over to a stage table.

Then run a job every minute or so to query the stage table, run some BCP commands to extract the data in a CSV format.


after insert
IF EXISTS (SELECT * from TEST_TABLE as c JOIN inserted AS I ON c.column = i.column where i.columnb = ‘Condition’)
INSERT INTO stage(column1, column2, column3) SELECT column1, column2, column3 from inserted

Export Call

declare @cmd varchar(2000)
select @cmd = ‘bcp “select * from stage” queryout c:\audit_’+convert(varchar(8),getdate(),112)+’_’+Replace(CONVERT(CHAR(8),(CONVERT(DATETIME,CURRENT_TIMESTAMP,113)),114), ‘:’, ”)+’.csv -t, -T -c’
EXEC xp_cmdshell @cmd
delete from stage

The Replace(CONVERT(CHAR(8),(CONVERT(DATETIME,CURRENT_TIMESTAMP,113)),114), ‘:’, ”) gives me miltary time…like that a lot. I will store that for later use.

Anyway, this works pretty well in testing. May need to make some tweaks before going live. I first tried running the BCP in the trigger itself, but had the trigger freeze problem that others reported and read some articles about how this is probably not the best idea. This way is not as realtime, but works well for what I need.