When implementing a statement-level trigger on a table, is it possible to obtain the OLD and NEW records for all affected rows?

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.

13
задан Adam Paynter 5 May 2011 в 23:23
поделиться