pankaj.kuchaliya (9/22/2010)
I have one confusion that is "trigger is executed when i insert in " ETL_Update" table . only one row can be inserted in this table at a single time. but in trigger we can insert multiple row in other table.
the behavior of a SQL Trigger is different than say, Oracle Triggers.
In Oracle, for exmaple, the trigger is fired once for each row....so an insert operation that has 5K rows fires the trigger 5K times.
SQL is different. the trigger fires once per batch..a insert into...select from that has 5k rows fires the trigger just once...so the design of the trigger has to take that into account.
as a general rule, if you've declared a variable inside a SQL trigger, the trigger is probably not written for a set based operation,at least if it is getting a value from either the INSERTED or DELETED tables.
since your calling a procedure inside that trigger, it will be called once per batch, not once for each row; that is why we asked if your business practice would only allow one row to be manipulated at a time; If that were true, while your trigger is not designed around a set based operation, it would at least infer that the call to the proc is acceptable based on your business practice.
any operation you do in a trigger should be written assuming more than one row is being inserted; most likely the logic withing the procedure being called will need to be moved inside the trigger instead of as a procedure call in order to affect multiple rows at a time.
Lowell