By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,415 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,415 IT Pros & Developers. It's quick & easy.

Fire an SQL Server Trigger on a Bulk Upload

P: 14
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.
Sep 18 '08 #1
Share this Question
Share on Google+
7 Replies


ck9663
Expert 2.5K+
P: 2,878
Read more here. The doc did not mention anything about SQL 2005 Express, though. But it might worth a try.

-- CK
Sep 18 '08 #2

P: 14
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.
Sep 19 '08 #3

Delerna
Expert 100+
P: 1,134
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.
Sep 19 '08 #4

P: 14
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.
Sep 19 '08 #5

ck9663
Expert 2.5K+
P: 2,878
I think DTS is using bcp, not BULK INSERT. Which means, by default, triggers are disabled.

-- CK
Sep 19 '08 #6

Delerna
Expert 100+
P: 1,134
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 ?
Sep 21 '08 #7

P: 14
I did get the triggers to fire on the Bulk Insert. Thanks for all of the help guys.
Sep 29 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.