Database Triggers Overview

The CREATE TRIGGER statement has a lot of permutations, but the vast majority of the questions I'm asked relate to basic DML triggers. Of those, the majority are related to people misunderstanding the order of the timing points and how they are affected by bulk-bind operations and exceptions. This article represents the bare minimum you should understand about triggers before you consider writing one.

• DML Triggers
  
  • The Basics
  • Timing Points
  • Bulk Binds
  • How Exceptions Affect Timing Points
  • Mutating Table Exceptions
  • Compound Triggers
  • Should you use triggers at all? (Facts, Thoughts and Opinions)
• Non-DML (Event) Triggers
• Enabling/Disabling Triggers

Related articles.

• Mutating Table Exceptions
• Trigger Enhancements in Oracle Database 11g Release 1
• Cross-Edition Triggers: Edition-Based Redefinition in Oracle Database 11g Release 2

DML Triggers

The Basics

For a full syntax description of the CREATE TRIGGER statement, check out the documentation shown here. The vast majority of the triggers I'm asked to look at use only the most basic syntax, described below.

CREATE [OR REPLACE] TRIGGER schema.trigger-name
{BEFORE | AFTER} dml-event ON table-name
[FOR EACH ROW]
[DECLARE ...]
BEGIN
  -- Your PL/SQL code goes here.
[EXCEPTION ...]
END;
/

The mandatory BEFORE or AFTER keyword and the optional FOR EACH ROW clause define the timing point for the trigger, which is explained below. 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.
    WHEN UPDATING THEN
      -- Include any code specific for when the trigger is fired from an UPDATE.
    WHEN DELETING THEN
      -- Include any code specific for when the trigger is fired from an DELETE.
  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, you should put it in a stored procedure defined as an autonomous transaction, shown here.

Timing Points

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, described here.

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.

The following code demonstrates the order in which the timing points are fired. It creates a test table, a package to hold shared data and a trigger for each of the timing points. Each trigger extends a collection defined in the package and stores a message with the trigger name and the current action it was triggered with. In addition, the after statement trigger displays the contents of the collection and empties it.

DROP TABLE trigger_test;
CREATE TABLE trigger_test (
  id           NUMBER         NOT NULL,
  description  VARCHAR2(50)   NOT NULL
);


CREATE OR REPLACE PACKAGE trigger_test_api AS

TYPE t_tab IS TABLE OF VARCHAR2(50);
g_tab t_tab := t_tab();
  
END trigger_test_api;
/

-- BEFORE STATEMENT
CREATE OR REPLACE TRIGGER trigger_test_bs_trg
BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
BEGIN
  trigger_test_api.g_tab.extend;
  CASE
    WHEN INSERTING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'BEFORE STATEMENT - INSERT';
    WHEN UPDATING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'BEFORE STATEMENT - UPDATE';
    WHEN DELETING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'BEFORE STATEMENT - DELETE';
  END CASE;
END;
/

-- BEFORE ROW
CREATE OR REPLACE TRIGGER trigger_test_br_trg
BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
FOR EACH ROW
BEGIN
  trigger_test_api.g_tab.extend;
  CASE
    WHEN INSERTING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'BEFORE EACH ROW - INSERT (new.id=' || :new.id || ')';
    WHEN UPDATING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'BEFORE EACH ROW - UPDATE (new.id=' || :new.id || ' old.id=' || :old.id || ')';
    WHEN DELETING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'BEFORE EACH ROW - DELETE (old.id=' || :old.id || ')';
  END CASE;
END trigger_test_br_trg;
/

-- AFTER ROW
CREATE OR REPLACE TRIGGER trigger_test_ar_trg
AFTER INSERT OR UPDATE OR DELETE ON trigger_test
FOR EACH ROW
BEGIN
  trigger_test_api.g_tab.extend;
  CASE
    WHEN INSERTING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'AFTER EACH ROW - INSERT (new.id=' || :new.id || ')';
    WHEN UPDATING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'AFTER EACH ROW - UPDATE (new.id=' || :new.id || ' old.id=' || :old.id || ')';
    WHEN DELETING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'AFTER EACH ROW - DELETE (old.id=' || :old.id || ')';
  END CASE;
END trigger_test_ar_trg;
/

-- AFTER STATEMENT
CREATE OR REPLACE TRIGGER trigger_test_as_trg
AFTER INSERT OR UPDATE OR DELETE ON trigger_test
BEGIN
  trigger_test_api.g_tab.extend;
  CASE
    WHEN INSERTING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'AFTER STATEMENT - INSERT';
    WHEN UPDATING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'AFTER STATEMENT - UPDATE';
    WHEN DELETING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'AFTER STATEMENT - DELETE';
  END CASE;
  
  FOR i IN trigger_test_api.g_tab.first .. trigger_test_api.g_tab.last LOOP
    DBMS_OUTPUT.put_line(trigger_test_api.g_tab(i));
  END LOOP;
  trigger_test_api.g_tab.delete;
END trigger_test_as_trg;
/

Querying the USER_OBJECTS view shows us the object are present and valid.

COLUMN object_name FORMAT A20

SELECT object_name, object_type, status FROM user_objects;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
TRIGGER_TEST_API     PACKAGE             VALID
TRIGGER_TEST         TABLE               VALID
TRIGGER_TEST_BS_TRG  TRIGGER             VALID
TRIGGER_TEST_BR_TRG  TRIGGER             VALID
TRIGGER_TEST_AR_TRG  TRIGGER             VALID
TRIGGER_TEST_AS_TRG  TRIGGER             VALID

6 rows selected.

SQL>

The follow output shows the contents of the collection after each individual DML statement.

SQL> SET SERVEROUTPUT ON

SQL> INSERT INTO trigger_test VALUES (1, 'ONE');
BEFORE STATEMENT - INSERT
BEFORE EACH ROW - INSERT (new.id=1)
AFTER EACH ROW - INSERT (new.id=1)
AFTER STATEMENT - INSERT

1 row created.

SQL> INSERT INTO trigger_test VALUES (2, 'TWO');
BEFORE STATEMENT - INSERT
BEFORE EACH ROW - INSERT (new.id=2)
AFTER EACH ROW - INSERT (new.id=2)
AFTER STATEMENT - INSERT

1 row created.

SQL> UPDATE trigger_test SET id = id;
BEFORE STATEMENT - UPDATE
BEFORE EACH ROW - UPDATE (new.id=2 old.id=2)
AFTER EACH ROW - UPDATE (new.id=2 old.id=2)
BEFORE EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER STATEMENT - UPDATE

2 rows updated.

SQL> DELETE FROM trigger_test;
BEFORE STATEMENT - DELETE
BEFORE EACH ROW - DELETE (old.id=2)
AFTER EACH ROW - DELETE (old.id=2)
BEFORE EACH ROW - DELETE (old.id=1)
AFTER EACH ROW - DELETE (old.id=1)
AFTER STATEMENT - DELETE

2 rows deleted.

SQL> ROLLBACK;

Rollback complete.

SQL>

From this we can see there is a single statement level before and after timing point, regardless of how many rows the individual statement touches, as well as a row level timing point for each row touched by the statement.

The same is true for an "INSERT ... SELECT" statement, shown below.

SET SERVEROUTPUT ON

INSERT INTO trigger_test
SELECT level, 'Description for ' || level
FROM   dual
CONNECT BY level <= 5;

BEFORE STATEMENT - INSERT
BEFORE EACH ROW - INSERT (new.id=1)
AFTER EACH ROW - INSERT (new.id=1)
BEFORE EACH ROW - INSERT (new.id=2)
AFTER EACH ROW - INSERT (new.id=2)
BEFORE EACH ROW - INSERT (new.id=3)
AFTER EACH ROW - INSERT (new.id=3)
BEFORE EACH ROW - INSERT (new.id=4)
AFTER EACH ROW - INSERT (new.id=4)
BEFORE EACH ROW - INSERT (new.id=5)
AFTER EACH ROW - INSERT (new.id=5)
AFTER STATEMENT - INSERT

5 rows created.

SQL> ROLLBACK;

Rollback complete.

SQL>

Bulk Binds

In the previous section we've seen what the timing points look like for individual statements. So are they the same for bulk binds? That depends on whether you are doing bulk inserts, updates or deletes using the FORALL statement. The following code builds a collection of 5 records, then uses that to drive bulk inserts, updates and deletes on the TRIGGER_TEST table. The triggers from the previous section will reveal the timing points that are triggered.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_trigger_test_tab IS TABLE OF trigger_test%ROWTYPE;
  l_tt_tab t_trigger_test_tab := t_trigger_test_tab();
BEGIN
  FOR i IN 1 .. 5 LOOP
    l_tt_tab.extend;
    l_tt_tab(l_tt_tab.last).id := i;
    l_tt_tab(l_tt_tab.last).description := 'Description for ' || i;
  END LOOP;
  
  DBMS_OUTPUT.put_line('*** FORALL - INSERT ***');
  -- APPEND_VALUES hint is an 11gR2 feature, but doesn't affect timing points.
  FORALL i IN l_tt_tab.first .. l_tt_tab.last
    INSERT /*+ APPEND_VALUES */ INTO trigger_test VALUES l_tt_tab(i);
    
  DBMS_OUTPUT.put_line('*** FORALL - UPDATE ***');
  -- Referencing collection columns in FORALL is only supported in 11g.
  FORALL i IN l_tt_tab.first .. l_tt_tab.last
    UPDATE trigger_test SET description = l_tt_tab(i).description WHERE id = l_tt_tab(i).id;
    
  DBMS_OUTPUT.put_line('*** FORALL - DELETE ***');
  -- Referencing collection columns in FORALL is only supported in 11g.
  FORALL i IN l_tt_tab.first .. l_tt_tab.last
    DELETE FROM trigger_test WHERE id = l_tt_tab(i).id;
    
  ROLLBACK;
END;
/

The output from this code is shown below. Notice how the statement level triggers only fire once at the start and end of the bulk insert operation, but fire on a row-by-row basis for the bulk update and delete operations. Make sure you understand your timing points when using bulk binds or you may get unexpected results.

*** FORALL - INSERT ***
BEFORE STATEMENT - INSERT
BEFORE EACH ROW - INSERT (new.id=1)
AFTER EACH ROW - INSERT (new.id=1)
BEFORE EACH ROW - INSERT (new.id=2)
AFTER EACH ROW - INSERT (new.id=2)
BEFORE EACH ROW - INSERT (new.id=3)
AFTER EACH ROW - INSERT (new.id=3)
BEFORE EACH ROW - INSERT (new.id=4)
AFTER EACH ROW - INSERT (new.id=4)
BEFORE EACH ROW - INSERT (new.id=5)
AFTER EACH ROW - INSERT (new.id=5)
AFTER STATEMENT - INSERT
*** FORALL - UPDATE ***
BEFORE STATEMENT - UPDATE
BEFORE EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER STATEMENT - UPDATE
BEFORE STATEMENT - UPDATE
BEFORE EACH ROW - UPDATE (new.id=2 old.id=2)
AFTER EACH ROW - UPDATE (new.id=2 old.id=2)
AFTER STATEMENT - UPDATE
BEFORE STATEMENT - UPDATE
BEFORE EACH ROW - UPDATE (new.id=3 old.id=3)
AFTER EACH ROW - UPDATE (new.id=3 old.id=3)
AFTER STATEMENT - UPDATE
BEFORE STATEMENT - UPDATE
BEFORE EACH ROW - UPDATE (new.id=4 old.id=4)
AFTER EACH ROW - UPDATE (new.id=4 old.id=4)
AFTER STATEMENT - UPDATE
BEFORE STATEMENT - UPDATE
BEFORE EACH ROW - UPDATE (new.id=5 old.id=5)
AFTER EACH ROW - UPDATE (new.id=5 old.id=5)
AFTER STATEMENT - UPDATE
*** FORALL - DELETE ***
BEFORE STATEMENT - DELETE
BEFORE EACH ROW - DELETE (old.id=1)
AFTER EACH ROW - DELETE (old.id=1)
AFTER STATEMENT - DELETE
BEFORE STATEMENT - DELETE
BEFORE EACH ROW - DELETE (old.id=2)
AFTER EACH ROW - DELETE (old.id=2)
AFTER STATEMENT - DELETE
BEFORE STATEMENT - DELETE
BEFORE EACH ROW - DELETE (old.id=3)
AFTER EACH ROW - DELETE (old.id=3)
AFTER STATEMENT - DELETE
BEFORE STATEMENT - DELETE
BEFORE EACH ROW - DELETE (old.id=4)
AFTER EACH ROW - DELETE (old.id=4)
AFTER STATEMENT - DELETE
BEFORE STATEMENT - DELETE
BEFORE EACH ROW - DELETE (old.id=5)
AFTER EACH ROW - DELETE (old.id=5)
AFTER STATEMENT - DELETE

PL/SQL procedure successfully completed.

SQL>

How Exceptions Affect Timing Points

If an exception is raised by the DML itself or by the trigger code, no more timing points are triggered. This means the after statement trigger is not fired, which can be a problem if you are using the after statement timing point to do some important processing. To demonstrate this we will force an exception in the after row trigger.

CREATE OR REPLACE TRIGGER trigger_test_ar_trg
AFTER INSERT OR UPDATE OR DELETE ON trigger_test
FOR EACH ROW
BEGIN
  trigger_test_api.g_tab.extend;
  CASE
    WHEN INSERTING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'AFTER EACH ROW - INSERT (new.id=' || :new.id || ')';
    WHEN UPDATING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'AFTER EACH ROW - UPDATE (new.id=' || :new.id || ' old.id=' || :old.id || ')';
    WHEN DELETING THEN
      trigger_test_api.g_tab(trigger_test_api.g_tab.last) := 'AFTER EACH ROW - DELETE (old.id=' || :old.id || ')';
  END CASE;
  RAISE_APPLICATION_ERROR(-20000, 'Forcing an error.');
END trigger_test_ar_trg;
/

When we perform an insert against the table we can see the expected error, but notice there is no timing point information displayed.

SET SERVEROUTPUT ON

INSERT INTO trigger_test VALUES (1, 'ONE');
            *
ERROR at line 1:
ORA-20000: Forcing an error.
ORA-06512: at "TEST.TRIGGER_TEST_AR_TRG", line 11
ORA-04088: error during execution of trigger 'TEST.TRIGGER_TEST_AR_TRG'

SQL>

This is because the after statement trigger did not fire. This also means that the collection was never cleared down. The following code will display the contents of the collection and clear it down.

BEGIN
  FOR i IN trigger_test_api.g_tab.first .. trigger_test_api.g_tab.last LOOP
    DBMS_OUTPUT.put_line(trigger_test_api.g_tab(i));
  END LOOP;
  trigger_test_api.g_tab.delete;
END;
/

BEFORE STATEMENT - INSERT
BEFORE EACH ROW - INSERT (new.id=1)
AFTER EACH ROW - INSERT (new.id=1)

PL/SQL procedure successfully completed.

SQL>

So all timing points executed as expected until the exception was raised, then the statement just stopped, without firing the after statement trigger. If the after statement trigger was responsible for anything important, like cleaning up the contents of the collection, we are in trouble. So once again, make sure you understand how the timing points are triggered, or you could get unexpected behavior.

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 as 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.
• 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.

Non-DML (Event) Triggers

Non-DML triggers, also known as event and system triggers, are can be split into two categories: DDL events and database events.

The syntax for both are similar, with the full syntax shown here and a summarized version below.

CREATE [OR REPLACE] TRIGGER trigger-name
{ BEFORE | AFTER } event [OR event]...
ON { [schema.] SCHEMA | DATABASE }
[DECLARE ...]
BEGIN
  -- Your PL/SQL code goes here.
[EXCEPTION ...]
END;
/

A single trigger can be used for multiple events of the same type (DDL or database). The trigger can target a single schema or the whole database. Granular information about triggering events can be retrieved using event attribute functions.

• Event Attribute Functions
• Event Attribute Functions for Database Event Triggers
• Event Attribute Functions for Client Event Triggers

Valid events are listed below. For a full description click the link.

• DDL Events : ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DISASSOCIATE STATISTICS, DROP, GRANT, NOAUDIT, RENAME, REVOKE, TRUNCATE, DDL
• Database Events : AFTER STARTUP, BEFORE SHUTDOWN, AFTER DB_ROLE_CHANGE, AFTER SERVERERROR, AFTER LOGON, BEFORE LOGOFF, AFTER SUSPEND

Of all the non-DML triggers, the one I use the most is the AFTER LOGON trigger. Amongst other things, this is is really handy for setting the CURRENT_SCHEMA flag for an application user session.

CREATE OR REPLACE TRIGGER app_user.after_logon_trg
AFTER LOGON ON app_user.SCHEMA
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCHEMA_OWNER';
END;
/

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;

For more information see:

• CREATE TRIGGER Statement