MacLochlainns Weblog

Oracle Trigger Basics

Once you master the basics of inserting, updating, and deleting data from the database, you typically learn about database triggers. Database triggers are coding elements that let you manage events beyond the limit of database constraints.

Before you can appreciate the power of database triggers, you need to understand what database constraints can and can’t do. Then, together we’ll explore how you can implement database triggers.
Database constraints let you manage events. A primary key constraint guarantees a column or a set of columns are unique and not null. A foreign key constraint guarantees a column only contains a value or set of values found in the primary key. A not null constraint makes a column mandatory when you insert or update a row in a table. A unique constraint guarantees a column or set of columns only exist in one row of a table. A check constraint guarantees a column’s value must comply with a set of rules defined with a row of data.

Database constraints do have limits. For example, a foreign key constraint doesn’t guarantee the right foreign key value because it only guarantees a foreign key value is a valid value in a list of possible values. That means it’s possible to insert or update a foreign key column or set of columns with an incorrect foreign key value. Only a database trigger can guarantee the insert or update of a correct foreign key value. The database trigger verifies the correct behavior by validating conditions before an insert or update.

While a unique constraint guarantees uniqueness and a check constraint guarantees compliance against a set of rules in a row, only a database trigger can guarantee the maximum number of like rows in a table that comply with a rule. Also, there is no constraint that manages inserts, updates, and deletes with dependencies on data in other tables.

A Data Manipulation Language (DML) trigger lets you manage these shortfalls and more. You have two options when implementing DML triggers. One implements a statement trigger and the other implements a row-level trigger. A statement-level trigger runs once for any and all rows affected by an INSERT, UPDATE, or DELETE statement. A row-level trigger runs once for each row affected by an INSERT, UPDATE, or DELETE statement.

Both of these triggers have two components – a trigger and a trigger body. The trigger defines what event to manage and the trigger body implements the logic that manages the event.

Statement-Level Triggers

You should create two tables to work with statement-level triggers. The first is the avenger table and the second is the avenger_log table.  Your inserts, updates, and deletes to the avenger table act as events that fire triggers. Statement-level triggers can be defined to run before or after INSERT, UPDATE, and DELETE statements. Statement-level triggers are narrowly scoped events and they log message data to the avenger_log table.

This is the definition of the avenger table:

SQL> CREATE TABLE avenger
  2  ( avenger_id    NUMBER
  3  , avenger_name  VARCHAR2(30)
  4  , first_name    VARCHAR2(20)
  5  , last_name     VARCHAR2(20));

This is the definition of the avenger_log table:

SQL> CREATE TABLE avenger_log
  2  ( avenger_log_id  NUMBER
  3  , trigger_name    VARCHAR2(30)
  4  , trigger_timing  VARCHAR2(6)
  5  , trigger_event   VARCHAR2(6)
  6  , trigger_type    VARCHAR2(12));

The following avenger_t1 creates a BEFORE INSERT statement trigger:

SQL> CREATE OR REPLACE TRIGGER avenger_t1
  2    BEFORE INSERT ON avenger
  3  DECLARE
  4    /* Declare local trigger-scope variables. */
  5    lv_sequence_id    NUMBER := avenger_log_s.NEXTVAL;
  6    lv_trigger_name   VARCHAR2(30) := 'AVENGER_T1';
  7    lv_trigger_event  VARCHAR2(6) := 'INSERT';
  8    lv_trigger_type   VARCHAR2(12) := 'STATEMENT';
  9    lv_trigger_timing VARCHAR2(6) := 'BEFORE';
 10  BEGIN
 11    /* Log event into the avenger_log table. */
 12    INSERT INTO avenger_log
 13    ( avenger_log_id
 14    , trigger_name
 15    , trigger_event
 16    , trigger_type
 17    , trigger_timing )
 18    VALUES
 19    ( lv_sequence_id
 20    , lv_trigger_name
 21    , lv_trigger_event
 22    , lv_trigger_type
 23    , lv_trigger_timing );
 24  END avenger_t1;
 25  /

Lines 1 and 2 declare the trigger. Lines 3 through 24 implements an anonymous PL/SQL block as the trigger’s body, and line 25 executes the trigger. Lines 6 through 9 store literal values for the trigger’s name, event, type, and timing. The trigger uses these literal values when logging events to the avenger_log table.

You access the data catalog information about triggers in the DBA_, ALL_, and USER_TRIGGERS views in a non-containerized database. Triggers also exist in those views for containerized databases (CDB). CDBs have an additional CDB_TRIGGERS view that stores triggers. The trigger body is stored in the TRIGGER_BODY column of those views in a LONG column.

You can create an AFTER STATEMENT trigger by simply changing the first two lines or the trigger declaration, as follows:

SQL> CREATE OR REPLACE TRIGGER avenger_t1
  2    AFTER INSERT ON avenger

Then, you need to change values of the string literals on lines 6, 7, and 9 as follows:

  6    lv_trigger_name   VARCHAR2(30) := 'AVENGER_T2';
  7    lv_trigger_event  VARCHAR2(6) := 'INSERT';
  8    lv_trigger_type   VARCHAR2(12) := 'STATEMENT';
  9    lv_trigger_timing VARCHAR2(6) := 'AFTER';

Compiling the database triggers, let’s insert a row into the avenger table, like this:

SQL> INSERT INTO avenger
  2  VALUES
  3  ( avenger_s.NEXTVAL
  4  ,'Captain America'
  5  ,'Steven'
  6  ,'Rogers');

Then, you can query the avenger_log table, like this:

SQL> COLUMN avenger_log_id FORMAT 999 HEADING "Avenger|Log ID #"
SQL> COLUMN trigger_name   FORMAT A12 HEADING "Trigger|Name"
SQL> COLUMN trigger_timing FORMAT A7  HEADING "Trigger|Timing"
SQL> COLUMN trigger_event  FORMAT A7  HEADING "Trigger|Event"
SQL> COLUMN trigger_type   FORMAT A12 HEADING "Trigger|Type"
SQL> SELECT * FROM avenger_log;

It returns two rows – one row from the avenger_t1 trigger and the other from the avenger_t2 trigger:

Avenger TRIGGER      TRIGGER TRIGGER TRIGGER
LOG ID # Name         TIMING  Event   TYPE
-------- ------------ ------- ------- ----------
       1 AVENGER_T2   AFTER   INSERT  STATEMENT
       2 AVENGER_T1   BEFORE  INSERT  STATEMENT

Both of the triggers use the avenger_log_s1 sequence. You may notice that the AFTER STATEMENT trigger ran before the BEFORE STATEMENT trigger. That shows you that triggers aren’t sequenced by default, even when you think that the timing event should sequence them.

Oracle lets you sequence triggers by using the FOLLOWS clause when you define database triggers. The following modifies the avenger_t2 by adding a FOLLOWS clause on line 3, and it uses ellipses to shorten the example:

SQL> CREATE OR REPLACE TRIGGER avenger_t2
  2    BEFORE INSERT ON avenger
  3    FOLLOWS avenger_t1
  4  DECLARE
  …
 11  BEGIN
 …
 25  END avenger_t2;
 26  /

The testing script drops and creates the avenger_log table before creating fresh copies of the avenger_t1 and avenger_t2 triggers. The script lets you re-query the avenger_log table without the baggage of the previous two rows.

Like before, it returns two rows – one row from the avenger_t1 trigger and the other from the avenger_t2 trigger:

Avenger TRIGGER      TRIGGER TRIGGER TRIGGER
LOG ID # Name         TIMING  Event   TYPE
-------- ------------ ------- ------- -----------
       1 AVENGER_T1   BEFORE  INSERT  STATEMENT
       2 AVENGER_T2   AFTER   INSERT  STATEMENT

You should note that the BEFORE STATEMENT trigger now runs before the AFTER STATEMENT trigger. The FOLLOWS clause lets you guarantee the order of trigger execution.

As you can see, statement-level triggers don’t give us the ability to see, change, or log the before and after values of data. You can do that with row-level triggers.

Row-Level Triggers

Row-level triggers let you see the initial column values you add into a table with an INSERT statement. Row-level triggers let you see existing column values and the column values provided by an UPDATE statement. The DELETE statement only provides the existing column values to a trigger because it removes the row from the database. Inside the row-level trigger you can change new values based on rules that you put in code inside the database trigger.

The avenger_log table requires major changes to support a row-level database trigger because it needs to store the old and new values of a table’s data column. Data columns hold values that describe an instance of the table. A data column or set of data columns should also define a unique key that makes each row unique in a table.

After engineering a table, you should also add a surrogate key column. A surrogate (stand-in) key column contains a value generated from a sequence, and a surrogate key is generally unrelated to the subject of a table. You use the natural key to find a unique row in a table, and you copy the surrogate key value when you want a foreign key to link another row with the row identified by the surrogate key.
Both the surrogate key column and natural key (one or more columns) should both identify unique rows. That means for every surrogate key there should be a natural key.

The data columns in the avenger table are the avenger_name, first_name, and last_name columns. You should define an old and new column for each of the data columns when you create a logging table.

This defines the new avenger_log table:

SQL> CREATE TABLE avenger_log
  2  ( avenger_log_id    NUMBER
  3  , trigger_name      VARCHAR2(30)
  4  , trigger_timing    VARCHAR2(6)
  5  , trigger_event     VARCHAR2(6)
  6  , trigger_type      VARCHAR2(12)
  7  , old_avenger_name  VARCHAR2(20)
  8  , old_first_name    VARCHAR2(20)
  9  , old_last_name     VARCHAR2(20)
 10  , new_avenger_name  VARCHAR2(20)
 11  , new_first_name    VARCHAR2(20)
 12  , new_last_name     VARCHAR2(20));

The first row-level database trigger you create runs when an INSERT statement adds a new row to the avenger table. The code exists below:

SQL> CREATE OR REPLACE TRIGGER avenger_t3
  2    BEFORE INSERT ON avenger
  3    FOR EACH ROW
  4  DECLARE
  5    /* Declare local trigger-scope variables. */
  6    lv_sequence_id    NUMBER := avenger_log_s.NEXTVAL;
  7    lv_trigger_name   VARCHAR2(30) := 'AVENGER_T3';
  8    lv_trigger_event  VARCHAR2(6) := 'INSERT';
  9    lv_trigger_type   VARCHAR2(12) := 'FOR EACH ROW';
 10    lv_trigger_timing VARCHAR2(6) := 'BEFORE';
 11  BEGIN
 12    /* Log event into the avenger_log table. */
 13    INSERT INTO avenger_log
 14    ( avenger_log_id
 15    , trigger_name
 16    , trigger_event
 17    , trigger_type
 18    , trigger_timing
 19    , old_avenger_name
 20    , old_first_name
 21    , old_last_name
 22    , new_avenger_name
 23    , new_first_name
 24    , new_last_name )
 25    VALUES
 26    ( lv_sequence_id
 27    , lv_trigger_name
 28    , lv_trigger_event
 29    , lv_trigger_type
 30    , lv_trigger_timing
 31    , :old.avenger_name
 32    , :old.first_name
 33    , :old.last_name
 34    , :NEW.avenger_name
 35    , :NEW.first_name
 36    , :NEW.last_name );
 37  END avenger_t3;
 38  /

Line 3 declares the avenger_t3 trigger as a row-level trigger. Lines 31 through 36 inserts the old and new values from the row of the avenger table when the INSERT statement runs with the following three values:

SQL> INSERT INTO avenger
  2  VALUES
  3  ( avenger_s.NEXTVAL
  4  ,'Capt. America'
  5  ,'Steven'
  6  ,'Rogers');

Since the script drops and recreates the avenger and avenger_log tables and drops the avenger_t1 and avenger_t2 statement-level triggers, you can write a query to return only the test row. The following anonymous PL/SQL block let’s you print the old and new column values next to one another. The program helps make the row-level trigger’s ability to see before and after values clear.

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED
SQL> BEGIN
  2    FOR i IN (SELECT * FROM avenger_log) LOOP
  3      dbms_output.put_line(
  4         'Trigger Name   ['
  5       || i.trigger_name||']');
  6      dbms_output.put_line(
  7         'Trigger Event  ['
  8       || i.trigger_event||']');
  9      dbms_output.put_line(
 10         'Trigger Type   ['
 11       || i.trigger_type||']');
 12      dbms_output.put_line(
 13         'Trigger Timing ['
 14       || i.trigger_timing||']');
 15      dbms_output.put_line(
 16         'Avenger Name   ['
 17       || i.old_avenger_name||']['
 18        || i.new_avenger_name||']');
 19      dbms_output.put_line(
 20         'First Name     ['
 21        || i.old_first_name||']['
 22        || i.new_first_name||']');
 23      dbms_output.put_line(
 24         'Last Name      ['
 25        || i.old_last_name||']['
 26        || i.new_last_name||']');
 27    END LOOP;
 28  END;
 29  /

This anonymous block prints the following from the avenger_log table:

TRIGGER Name   [AVENGER_T3]
TRIGGER Event  [INSERT]
TRIGGER TYPE   [FOR EACH ROW]
TRIGGER TIMING [BEFORE]
Avenger Name   [][Capt. America]
FIRST Name     [][Steven]
LAST Name      [][Rogers]

This has demonstrated how you write a row-level trigger against an INSERT event. You should note that the old values for the avenger_name, first_name, and last_name are null values between the square brackets. Next, you should examine how to write a row-level trigger against more than one type of event.

The Oracle Database lets you write individual triggers for INSERT, UPDATE, or DELETE statement, or a single trigger to manage INSERT, UPDATE, and DELETE events. The following modifies the avenger_t3 trigger so that it works for an INSERT, UPDATE, and DELETE events:

SQL> CREATE OR REPLACE TRIGGER avenger_t3
  2    BEFORE INSERT OR UPDATE OR DELETE ON avenger
  3    FOR EACH ROW

Line 2 of the previous trigger is where we change the avenger_t3 trigger to also work with UPDATE and DELETE events. Then, we need to change one other line and then add a small IF-block to the trigger.

Line 8 of the original trigger assigns a default value to the lv_trigger_event variable, but you need to remove the value assignment. The modified line looks like this:

  8    lv_trigger_event  VARCHAR2(6);

You also need to add an IF-block that manages Data Manipulation Language (DML) event functions. The IF-block should be the first thing in the execution block of the trigger body, and it should implement this logic:

 11  BEGIN
 12    /* Evaluate and assign event for logging. */
 13    IF   INSERTING THEN lv_trigger_event := 'INSERT';
 14    ELSIF UPDATING THEN lv_trigger_event := 'UPDATE';
 15    ELSIF DELETING THEN lv_trigger_event := 'DELETE';
 16    END IF;
 ...

The INSERTING event function on line 13 occurs when an INSERT statement activates the trigger. The UPDATING and DELETING event functions on lines 14 and 15 occur when a respective UPDATE or DELETE statement activity fires a trigger.

The following UPDATE statement now creates an event that the avenger_t3 trigger is monitoring:

SQL> UPDATE avenger
  2  SET    avenger_name = 'Captain America'
  3  WHERE  avenger_name = 'Capt. America';

Next, let’s test a DELETE statement with the following:

SQL> DELETE
  2  FROM    avenger
  3  WHERE   avenger_name = 'Captain America';

The following anonymous block program lets you see the log values inserted into the avenger_log table from the INSERT, UPDATE, and DELETE statement triggers:

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED
SQL> BEGIN
  2    FOR i IN (SELECT * FROM avenger_log) LOOP
  3      dbms_output.put_line(
  4         'Row Number     ['
  5       || i.avenger_log_id ||']['
  6       || i.trigger_event ||']');
  7      dbms_output.put_line(
  8         'Avenger Name   ['
  9       || i.old_avenger_name ||']['
 10        || i.new_avenger_name ||']');
 11      dbms_output.put_line(
 12         'First Name     ['
 13        || i.old_first_name ||']['
 14        || i.new_first_name ||']');
 15      dbms_output.put_line(
 16         'Last Name      ['
 17        || i.old_last_name ||']['
 18        || i.new_last_name ||']');
 19    END LOOP;
 20  END;
 21  /

The anonymous block returns the following:

Row Number     [1][INSERT]
Avenger Name   [][Capt. America]
First Name     [][Steven]
Last Name      [][Rogers]
Row Number     [2][UPDATE]
Avenger Name   [Capt. America][Captain America]
First Name     [Steven][Steven]
Last Name      [Rogers][Rogers]
Row Number     [3][DELETE]
Avenger Name   [Captain America][]
First Name     [Steven][]
Last Name      [Rogers][]

You should notice the old values for the INSERT event are missing because there isn’t a row before running the INSERT statement. Likewise, you should notice the new values for the DELETE event are missing because there isn’t a row after running a DELETE statement. Only the UPDATE event has both an old and new value because the row exists before and after any change. The old values hold the row’s values before the UPDATE statement and the new values hold the row’s values after the UPDATE statement.