Multiple rows update trigger

Question

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

Answer 1

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.

Answer 2

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 ....

Answer 3

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