Reference no: EM13818915

DML triggers can be defined for a combination of DML events by linking them together with the OR keyword. When a trigger is defined for multiple DML events, event-specific code can be defined using the INSERTING, UPDATING, DELETING flags.
Row level triggers can access new and existing values of columns using the ":NEW.column-name" and ":OLD.column-name" references, bearing in mind the following restrictions.

• Row-level INSERT triggers: Only ":NEW" references are possible as there is no existing row.

• Row-level UPDATE triggers: Both ":NEW" and ":OLD" references are possible. ":NEW" represents the new value presented in the DML statement that caused the trigger to fire. ":OLD" represents the existing value in the column, prior to the update being applied.

• Row-level DELETE triggers: Only ":OLD" references are possible as there is no new data presented in the triggering statement, just the existing row that is to be deleted.

Triggers can not affect the current transaction, so they can not contain COMMIT or ROLLBACK statements. If you need some code to perform an operation that needs to commit, regardless of the current transaction, you should put it in a stored procedure defined as an autonomous transaction.

DML triggers have four basic timing points for a single table.

• Before Statement: Trigger defined using the BEFORE keyword, but the FOR EACH ROW clause is omitted.

• Before Each Row: Trigger defined using both the BEFORE keyword and the FOR EACH ROW clause.

• After Each Row: Trigger defined using both the AFTER keyword and the FOR EACH ROW clause.

• After Statement: Trigger defined using the AFTER keyword, but the FOR EACH ROW clause.

Oracle allows you to have multiple triggers defined for a single timing point, but it doesn't guarantee execution order unless you use the FOLLOWS clause available in Oracle 11g.

Oracle allows more than one trigger to be created for the same timing point, but it has never guaranteed the execution order of those triggers. The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the

same timing point.

With the exception of Compound Triggers, the triggers for the individual timing points are self contained and can't automatically share state or variable information. The workaround for this is to use variables defined in packages to store information that must be in scope for all timing points.

Oracle 11g introduced the concept of compound triggers, which consolidate the code for all the timing points for a table, along with a global declaration section into a single code object. The global declaration section stays in scope for all timing points and is cleaned down when the statement has finished, even if an exception occurs.

A compound trigger allows code for one or more timing points for a specific object to be combined into a single trigger. The individual timing points can share a single global declaration section, whose state is maintained for the lifetime of the statement. Once a statement ends, due to successful completion or an error, the trigger state is cleaned up. In previous releases this type of functionality was only possible by defining multiple triggers whose code and global variables were defined in a separate package, but the compound trigger allows for a much tidier solution.

The triggering actions are defined in the same way as any other DML trigger, with the addition of the COMPOUND TRIGGER clause. The main body of the trigger is made up of an optional global declaration section and one or more timing point sections, each of which may contain a local declaration section whose state is not maintained.