Practice with Triggers - Part 2 - DML Triggers

A DML trigger is created on either a table or view, and its triggering event is composed of the DML statements DELETE, INSERT, and UPDATE. To create a trigger that fires in response to a MERGE statement, create triggers on the INSERT and UPDATE statements to which the MERGE operation decomposes.

A DML trigger is either simple or compound.

A simple DML trigger fires at exactly one of these timing points:

• Before the triggering statement runs

(The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger.)

• After the triggering statement runs

(The trigger is called an AFTER statement trigger or statement-level AFTER trigger.)

• Before each row that the triggering statement affects

(The trigger is called a BEFORE each row trigger or row-level BEFORE trigger.)

• After each row that the triggering statement affects

(The trigger is called an AFTER each row trigger or row-level AFTER trigger.)

A compound DML trigger created on a table or editioning view can fire at one, some, or all of the preceding timing points. Compound DML triggers help program an approach where you want the actions that you implement for the various timing points to share common data.

A simple or compound DML trigger that fires at row level can access the data in the row that it is processing.

An INSTEAD OF DML trigger is a DML trigger created on either a noneditioning view or a nested table column of a noneditioning view.

A crossedition trigger is a simple or compound DML trigger for use only in edition-based redefinition(EBR).

Except in an INSTEAD OF trigger, a triggering UPDATE statement can include a column list. With a column list, the trigger fires only when a specified column is updated. Without a column list, the trigger fires when any column of the associated table is updated.

The Basics

The most basic syntax, described below.

CREATE [OR REPLACE] TRIGGER schema.trigger_name

{BEFORE | AFTER | INSTEAD OF}  

{INSERT [OR] | UPDATE [OR] | DELETE}  

[OF col_name]  

ON table_name  

[REFERENCING OLD AS o NEW AS n]  

[FOR EACH ROW | FOR EACH STATEMENT]  

WHEN (condition)   

DECLARE 

   Declaration-statements 

BEGIN  

   Executable-statements 

EXCEPTION 

   Exception-handling-statements 

END; 

The mandatory BEFORE or AFTER keyword and the optional FOR EACH ROW clause define the timing point for the trigger. There are optional declaration and exception sections, like any other PL/SQL block, if required.

The "dml-event" can be one or more of the following.

INSERT

UPDATE

UPDATE FOR column-name [, column-name ...]

DELETE

DML triggers can be defined for a combination of DML events by linking them together with the OR keyword.

INSERT OR UPDATE OR DELETE

When a trigger is defined for multiple DML events, event-specific code can be defined using the INSERTING, UPDATING, DELETING flags.

CREATE OR REPLACE TRIGGER my_test_trg

BEFORE INSERT OR UPDATE OR DELETE ON my_table

FOR EACH ROW

BEGIN

  -- Flags are booleans and can be used in any branching construct.

  CASE

    WHEN INSERTING THEN

      -- Include any code specific for when the trigger is fired from an INSERT.

      -- Also fired for INSERT as part of a MERGE.

    WHEN UPDATING THEN

      -- Include any code specific for when the trigger is fired from an UPDATE.

      -- Also fired for UPDATE as part of a MERGE.

    WHEN DELETING THEN

      -- Include any code specific for when the trigger is fired from a DELETE.

      -- Does not fire for DELETE clause of a MERGE.

  END CASE;

END;

/

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, we should put it in a stored procedure defined as an autonomous transaction.

Autonomous Transactions

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction.

Autonomous transactions allow you to leave the context of the calling transaction, perform an independent transaction, and return to the calling transaction without affecting it's state. The autonomous transaction has no link to the calling transaction, so only committed data can be shared by both transactions.

For more information see:

 AUTONOMOUS_TRANSACTION

Mutating Table Exceptions

Row-level DML triggers are not allowed to query or perform any DML on the table that fired them. If they attempt to do so a mutating table exception is raised. This can become a little awkward when you have a parent-child relationship and a trigger on the parent table needs to execute some DML on the child table. If the child table has a foreign key (FK) back to the parent table, any DML on the child table will cause a recursive SQL statement to check the constraint. This will indirectly cause a mutating table exception. An example of mutating tables and a workaround for them can be found here.

 

Compound Triggers

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. An article about compound triggers and other trigger-related new features in 11g can be found here.

 

Should you use triggers at all? (Facts, Thoughts and Opinions)

I'm not a major fan of DML triggers, but I invariably use them on most systems. Here are a random selection of facts, thoughts and opinions based on my experience. Feel free to disagree.

• Adding DML triggers to tables affects the performance of DML statements on those tables. Lots of sites disable triggers before data loads then run cleanup jobs to "fill in the gaps" once the data loads are complete. If you care about performance, go easy on triggers.
• Doing non-transactional work in triggers (autonomous transactions, package variables, messaging and job creation) can cause problems when Oracle performs DML restarts. Be aware that a single DML statement may be restarted by the server, causing any triggers to fire multiple times for a single DML statement. If non-transactional code is included in triggers, it will not be rolled back with the DML before the restart, so it will execute again when the DML is restarted.
• If you must execute some large, or long-running, code from a trigger, consider decoupling the process. Get your trigger to create a job or queue a message, so the work can by picked up and done later.
• Spreading functionality throughout several triggers can make it difficult for developers to see what is really going on when they are coding, since their simple insert statement may actually be triggering a large cascade of operations without their knowledge. This can quickly lead to spaghetti solutions.
• It can be hard to track code dependencies when using triggers.
• Triggers inevitably get disabled by accident and their "vital" functionality is lost so you have to repair the data manually.
• If something is complex enough to require one or more triggers, you should probably place that functionality in a PL/SQL API and call that from your application, rather than issuing a DML statement and relying on a trigger to do the extra work for you. PL/SQL doesn't have all the restrictions associated with triggers, so it's a much nicer solution.
• I've conveniently avoided mentioning INSTEAD OF triggers up until now. I'm not saying they have no place and should be totally avoided, but if you find yourself using them a lot, you should probably either redesign your system, or use PL/SQL APIs rather than triggers. One place I have used them a lot was in a system with lots of object-relational functionality. Also another feature whose usage should be questioned.

Enabling/Disabling Triggers

Prior to Oracle 11g, triggers are always created in the enabled state. In Oracle 11g, triggers can now be created in the disabled state, shown here.

Specific triggers are disabled and enabled using the ALTER TRIGGER command.

ALTER TRIGGER trigger-name DISABLE;

ALTER TRIGGER trigger-name ENABLE;

All triggers for a table can be disabled and enabled using the ALTER TABLE command.

ALTER TABLE table-name DISABLE ALL TRIGGERS;

ALTER TABLE table-name ENABLE ALL TRIGGERS;

I hope you all have enjoyed reading this article. Comments are welcome....

Related Posts:

-Practice with Triggers - Part 1

-AUTONOMOUS_TRANSACTION
-TRIGGER - Examples