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.

TRIGGER

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

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
GO
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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s