Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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.

Related articles.

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

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.

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.

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.

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

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:

Hope this helps. Regards Tim...

Back to the Top.