Solved

Run the INSERT trigger once for each row inserted

Posted on 2004-04-21
3
516 Views
Last Modified: 2009-12-16
I have an AFTER INSERT trigger on a table (table1) which performs some update task in another table (table2) based on the record inserted in the first table. Now when multiple rows are inserted into table1 thru a single INSERT statement the trigger runs once and I am unable to update table2 properly.

My question is can we force an INSERT trigger to run once for each row inserted? Or should this be achieved by using a CURSOR for the 'inserted' table. I learnt that using CURSORs in triggers are not advisable. So what is the way out?
0
Comment
Question by:tknayak123
  • 2
3 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 10885979
>>can we force an INSERT trigger to run once for each row inserted<<
The trigger should fire ONCE
but
your trigger's code should handle batch updates.
It provides virtual tables "inserted" and "deleted" that make this possible

To summarize
- The problem is not with the trigger not beeing fired
- The problem is with your code that doesn't handle batch inserts

Post your code so that we can have a look

Hilaire

0
 

Author Comment

by:tknayak123
ID: 10886121
Thanx Hilaire for a very quick response. Yes, I just wanted to make myself sure that we cannot force the trigger to run as many times as the rows inserted. As per your reply, it cannot be done. Now I can modify my code to take care of batch updates.

But I still want to know whether you use CURSORs for "inserted" and "deleted" table as a common practice? Or you use some other logic.
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 250 total points
ID: 10886151
My advice would be NEVER use cursors unless there's no other solution.
Most of the times the same thing can be achieved whithout a cursor, using set-based operations

EG you can make an update to another table using a join on the inserted table

update a
set a.anycolumn = b.relatedcolumn
from <AnyTable> a inner join inserted b on a.PKID = b.PKID

Cursors are ressource-consuming AND triggers are resource-consuming too
The combination is a perfect CPU-time eater ...

Feel free to post you code if you think I can help more
Hilaire
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
SQL Server 2012 r2 - Make Temp Table Query Faster 5 44
sql query to calculate avaerage 21 40
SQL - Update field defined as Text 6 17
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question