In Oracle, you can write a row-level trigger by specifying the FOR EACH ROW
clause in the CREATE TRIGGER
statement:
CREATE TRIGGER MY_FANCY_TRIGGER
BEFORE UPDATE ON MY_TABLE
FOR EACH ROW
BEGIN
IF :OLD.my_id_column > 4 AND :NEW.some_other_column <> 'foo' THEN
-- ...
END IF;
END;
Such a trigger allows you to view the before and after versions of each affected row (:OLD
and :NEW
respectively). For example, the following statement will cause this trigger to execute once for each row in MY_TABLE
:
UPDATE MY_TABLE SET some_other_column = 'bar';
By eliminating the FOR EACH ROW
clause, the trigger becomes a statement-level trigger. This means that it only be executed once per statement, regardless of how many rows (if any) were affected by the statement. Unfortunately, statement-level triggers do not have the :OLD
and :NEW
variables available (because the number of affected rows many vary).
Is it possible to obtain the :OLD
and :NEW
values for all affected rows inside a statement-level trigger? I have some processing that I would prefer to only occur once per statement.