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.
CREATE TRIGGER Trigger on TEST_TABLE
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
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.