?
Solved

Run the INSERT trigger once for each row inserted

Posted on 2004-04-21
3
Medium Priority
?
551 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 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

850 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