If I have a statement that updates multiple rows, only the trigger will fire only on the first or last row that is being updated (not sure which one). I need to make a trigger that fires for ALL the records that are being updated into a particular table
-
ask questions more quietly. I have gone partially deafoxbow_lakes– oxbow_lakes2010-03-19 13:06:28 +00:00Commented Mar 19, 2010 at 13:06
-
1@sony: PLEASE DO NOT USE ALL-CAPS EVER! IT LOOKS LIKE YOU'RE SHOUTING!John Saunders– John Saunders2010-03-19 13:11:01 +00:00Commented Mar 19, 2010 at 13:11
-
Which database software? Trigger support varies wildly by vendor.Shannon Severance– Shannon Severance2010-03-19 16:32:33 +00:00Commented Mar 19, 2010 at 16:32
-
SUggest you provide us withthe trigger code and the database you are using, then we can help you make it do what you need it to do.HLGEM– HLGEM2010-03-19 19:26:15 +00:00Commented Mar 19, 2010 at 19:26
Add a comment
|
3 Answers
Assuming SQL Server, A trigger only fires once per update, regardless of the number of rows that are updated. If you need to carry out some additional logic based on updates to multiple rows you can access the changed records by looking at the INSERTED and DELETED logical tables that are accessible in the context of a trigger.
Comments
You have not specified the database ..... In Oracle a trigger can be defined to fire for individual rows and based on the type of transaction:
CREATE OR REPLACE TRIGGER BIUDR_MY_TABLE
BEFORE INSERT OR UPDATE OR DELETE
ON MY_TABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
pk PLS_INTEGER;
BEGIN
etc ....
Comments
You just need to indicate if your trigger needs to be executed "FOR EACH ROW" or "FOR EACH STATEMENT". Adding one of these two clauses in the trigger definition will tell the DBMS when to execute the trigger (most, but not all, DBMSs support it). If you don't indicate this clause then the DBMS uses the default option which in your case seems to be the FOR EACH STATEMENT option, and that's why your trigger only fires one for each update sentence, regardless of how many rows you are updating
