Fire an SQL Server Trigger on a Bulk Upload

[srkidd12]

Hello,

Does anyone know how to get a trigger to fire while you are uploading multiple records into a SQL Server 2005 Express DB using the DTS Wizard?

On insert of a person's record I want the trigger to find their id# from another table and update their information accordingly.

I have the trigger built, but it will not fire when I use the DTS Wizard.

Please help. Thanks.

[ck9663]

Read more here. The doc did not mention anything about SQL 2005 Express, though. But it might worth a try.

-- CK

[srkidd12]

What about when you execute the DTS Wizard? I am trying to insert new rows and update the information in other tables using the triggers. The triggers will not fire. Any ideas. I'm searching the Web up and down.

Thanks for your help.

[Delerna]

Why would't you extend the insert query so that it obtains the ID at the same time as inserting the record? You wouldn't need a trigger then.

[srkidd12]

Why would't you extend the insert query so that it obtains the ID at the same time as inserting the record? You wouldn't need a trigger then.

I am not using a query. I am using the dts wizard that came with SQL Server 2005 Express.

You wouldn't know how to get the trigger to fire in that instance would you?

I may have to look at the bulk insert function.

[ck9663]

I think DTS is using bcp, not BULK INSERT. Which means, by default, triggers are disabled.

-- CK

[Delerna]

I am not using a query. I am using the dts wizard that came with SQL Server 2005 Express.
Thanks for your help.

Sorry bad wording on my part. Yes, you are using DTS but DTS is using query in the data pump to select the records from the destination and/or insert them into a table in your database.

What I meant by extending the query (bad wording... should have said extend the DTS) was to
Add an 'on success' workflow pipe onto the destination server and attatch it to an SQL task so that the SQL Task executes when the data pump transfer is successful.

In the SQL task you place the code that your trigger would execute

That was hard to explain, I hope I am making sense?

Also, just noticed you are using SQL Express. Not had any experience with express. Does it have that sort of control over DTS ?

[srkidd12]

I did get the triggers to fire on the Bulk Insert. Thanks for all of the help guys.