Menu
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.
235
Old and new values are available in both BEFORE and AFTER row triggers. A new column value can be assigned in a BEFORE row trigger, but not in an AFTER row trigger (because the triggering statement takes effect before an AFTER row trigger is fired).
Row level triggers executes once for each and every row in the transaction. Statement level triggers executes only once for each single transaction. Specifically used for data auditing purpose. Used for enforcing all additional security on the transactions performed on the table.
A row-level trigger fires once for each row that is affected by a triggering event. For example, if deletion is defined as a triggering event for a particular table, and a single DELETE statement deletes five rows from that table, the trigger fires five times, once for each row.
A statement trigger fires once per triggering event and regardless of whether any rows are modified by the insert, update, or delete event. row triggers. A row trigger fires once for each row affected by the triggering event. If no rows are affected, the trigger does not fire.
One approach is the one suggested by Justin Cave to store away information in row level triggers in a separate package collections.
If you are using 11g then the right approach will be to use Compound triggers. This avoids the creation of separate package to hold the keys collection- it can be done in the trigger itself,
138
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
