Run the INSERT trigger once for each row inserted
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?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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