Solved

Run the INSERT trigger once for each row inserted

Posted on 2004-04-21
3
522 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Powershell v3 - SQLCMD 3 26
Select single row of data for each ID in Select Statement 7 26
SQL, add where clause 5 23
SQL Dump exec output to table 3 21
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

808 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