Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Run the INSERT trigger once for each row inserted

Posted on 2004-04-21
3
Medium Priority
?
547 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 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