January 11, 2010 at 9:57 pm
I am running a batch update on a table having a trigger defined. This trigger executes a stored proc which insert records into 2 different tables.
Now with batch updates, the inserted and deleted tables will have the multiple rows. Is there any way through which i can execute the stored proc in the trigger for each row in the inserted table?
i don't prefer to use a cursor inside the trigger to iterate through all the records of inserted table. Looping through the records seems to be an option but it will slow down the whole process.
I have seen the article http://qa.sqlservercentral.com/articles/Triggers/64214/ by Jack Corbett which suggests to use set based approach rather than looping through the records. With my situation how can i execute a proc for each record using set based approach? i havent read or seen like it before.
Any suggestions are appreciated.
January 11, 2010 at 10:36 pm
It depends on your approach. you can move the code that was being executed in the stored procedure directly into the trigger; that way you can insert from the INSERTED and DELETED tables as needed.
another alternate is to do it outside of the trigger, by using the OUTPUT command to insert info from the INSERTED and DELETED tables into a temp table, then do your updates from that table.
if you are stuck with the procedure, I don't see any alternative other than a cursor in the trigger body.
stating the obvious, although you can make a function apply across multiple rows, a function cannot do inserts/updates/deletes, so you cannot switch to a function to do it.
Lowell
January 12, 2010 at 5:27 pm
I used Update trigger to fire the stored proc per record. I was thinking triggers in SQL Server work the same way it does in Oracle but unfortunately i was wrong. I haven't dealt with multiple row update trigger before so it was a new experience. :hehe:
In my case, i have to execute the stored proc per record and now instead of using an update trigger, i am using while loops to execute the proc per record.
Also i have taken the approach of executing the stored proc in batches. I am taking a subset of data (200000 rows) into a tmp table from base table of 20 mil records. Then i execute the stored proc per row from this tmp table rather than going to base table and executing it from there.
The reason behind following this approach is to reduce the seek time to fetch the record from the massive base table.
January 12, 2010 at 5:45 pm
more details are needed i think; i think you are misusing the trigger functionality.
it sounds like you are reprocessing every record in the 10 million row table if someone fires the trigger, not doing work related to the specific rows that were affected in the trigger.
an example for me is if i update 10 rows, or even one million rows, i should not have to go through the entire table.
if you are inserting/updating other tables, can any of the data you are creating be replaced with views of the main table instead?
give us some specifics..DDL statements, what the procedure is actually doing, etc so we can help you better.
Lowell
January 13, 2010 at 8:38 am
A hack that can work is to create temp tables in the trigger from the inserted and deleted tables and using them in the stored procedure. This is definitely a hack. Other options:
1. Move the sp logic into the trigger - mentioned by Lowell
2. Move this logic out of the trigger using OUTPUT as mentioned by Lowell
3. Make this logic asynchronous by queue the changes into a real table and processing those rows using a job or do the same using Service Broker (probably the better way).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2011 at 4:39 am
Your solution:
I used Update trigger to fire the stored proc per record. I was thinking triggers in SQL Server work the same way it does in Oracle but unfortunately i was wrong. I haven't dealt with multiple row update trigger before so it was a new experience. [Hehe]
In my case, i have to execute the stored proc per record and now instead of using an update trigger, i am using while loops to execute the proc per record.
Also i have taken the approach of executing the stored proc in batches. I am taking a subset of data (200000 rows) into a tmp table from base table of 20 mil records. Then i execute the stored proc per row from this tmp table rather than going to base table and executing it from there.
The reason behind following this approach is to reduce the seek time to fetch the record from the massive base table.
I saw your above solution regarding the batch updates but
It would be kind of you,if you please send me the sql query regarding the batch updates with the trigger on minu88@gmail.com.
I was having the problem with the bulk updates of rows from one table to another with the help of a trigger
Thanks and Regards
Minakshi
April 28, 2011 at 4:48 am
minu88 (4/28/2011)
It would be kind of you,if you please send me the sql query regarding the batch updates with the trigger on minu88 at gmail dot com.I was having the problem with the bulk updates of rows from one table to another with the help of a trigger
Minakshi
The traditional method is for you to have a go at doing this yourself, then post back if you run into any specific difficulties.
By the way, if you post your literal e-mail address on a public forum, you'll get spammed. I'd advise you to post it the way I've changed it in the quoted box above.
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply