Solved

Run the INSERT trigger once for each row inserted

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now