This article demonstrates how you can write critical and non-critical row-level triggers. You may ask yourself, what are critical and non-critical triggers? That’s a great question. A critical trigger stops processing and raises an exception within the scope of an Application Programming Interface (API). An API is typically a series of end-user forms that help you solve business problems. A non-critical trigger either allows users to perform undesired behavior or it automatically fixes undesired behavior by preventing it. Non-critical triggers may log events but they don’t typically raise exceptions to the API.

Next, you’re probably asking yourself if critical and non-critical triggers are important. That’s also a great question. The answer is they’re very important and a key part of any database-centric application software solution.

If you’re new to database triggers, you can read the DML Trigger Basic article on this site to get an introduction. By way of review, you can write database triggers against DDL or DML statements. DML triggers can be either statement-level or row-level triggers.

The difference between a statement-level and row-level trigger is simple. A statement-level trigger runs once for any INSERT, UPDATE, or DELETE statement, which means you can’t inspect the specific rows that a DML statement affects. A row-level trigger runs once for each row affected by an INSERT, UPDATE, or DELETE statement.

Row-level database triggers give us the most granular (fancy word for detailed) view of transactions in your application. They’re also the best suited to logging changes happening with your data. The examples in this article will use DML row-level database triggers.

Business Logic

The article creates some tables for the examples, and the tables use traditional Oracle sequences and triggers. That’s because using sequences and triggers is the closest to how Oracle APEX creates tables. Many readers are familiar with how APEX works. After we create the tables, sequences, and basic automatic numbering database triggers, you will learn how to create non-critical triggers. The last section shows you how to create critical triggers.

It’s helpful to have a basic business problem when you work with so many moving parts. I chose a business problem that should be familiar to most people. The example uses a human resource professional. A human resource professional creates new employees when they join a company. Company policy sometimes dictates the convention for personal names. For example, they may restrict multipart last names. That means when you want to enter a multipart last name; they replace the whitespace with a hyphen.

The example business case requires that all last names must have hyphens. This means that the company disallows multipart last names. While this may seem old fashioned, it’s a simple business process to model, and it lets you see how to work with non-critical and critical database triggers.

So, here are our two use cases:

Non-critical Use Case

A human resource professional may try to enter a multipart last name with whitespace between parts. The entry may be intentional or simply a mistake. Assuming a positive mental attitude, you should assume the human resource profession doesn’t understand the policy. That means our triggers shouldn’t raise an exception when initially entering a value. The insert trigger should only log the attempt to enter non-conforming data. Initial entries, like this, are made through INSERT statements.

Critical Use Case

What the same human resource professional does when they notice that they weren’t able to enter a multipart last name becomes important. A critical trigger becomes necessary when the human resource professional tries to change a hyphenated name into a multipart name. The API uses an UPDATE statement to change an existing value with a new value. There is no use case when the human resource professional accepts the change to a hyphenated name.

 The following steps you through how you create a framework for the non-critical and critical triggers. The framework uses three tables.

Framework

The non-critical trigger only uses two of those tables. The non-critical trigger is an INSERT trigger and the critical trigger is an UPDATE trigger. The application_user table will contain information about our authorized users; and the employee table will be the target for our non-critical and critical triggers.

The following creates the application_user table with this statement:

SQL> CREATE TABLE application_user

  2  ( application_user_id    NUMBER

  3  , application_user_name  VARCHAR2(30)  CONSTRAINT application_user_nn1 NOT NULL

  4  , created_by             NUMBER        CONSTRAINT application_user_nn2 NOT NULL

  5  , creation_date          DATE          CONSTRAINT application_user_nn3 NOT NULL

  6  , last_updated_by        NUMBER        CONSTRAINT application_user_nn4 NOT NULL

  7  , last_update_date       DATE          CONSTRAINT application_user_nn5 NOT NULL

  8  , CONSTRAINT application_user_pk  PRIMARY KEY (application_user_id)

  9  , CONSTRAINT application_user_fk1 FOREIGN KEY (created_by)

 10    REFERENCES application_user (application_user_id)

 11  , CONSTRAINT application_user_fk2 FOREIGN KEY (last_updated_by)

 12    REFERENCES application_user (application_user_id));

The application_user_seq supports a surrogate key for the application_user table. You create it with the following statement:

SQL> CREATE SEQUENCE application_user_seq;

The application_user_t1 trigger ensures a sequence value is generated for the application_user_id column. You create the application_user_t1 trigger with this statement:

SQL> CREATE OR REPLACE TRIGGER application_user_t1

  2    BEFORE INSERT ON application_user

  3    FOR EACH ROW

  4  BEGIN

  5    /* Check for a empty image_id primary key column value,

  6      and assign the next sequence value when it is missing. */

  7    IF :new.application_user_id IS NULL THEN

  8    SELECT application_user_seq.NEXTVAL

  9    INTO  :new.application_user_id

 10    FROM  dual;

 11    END IF;

 12  END;

 13  /

You will need at least one row in the application_user table to test the non-critical and critical triggers. The following insert a single row into the application_user table:

SQL> INSERT INTO application_user

  2  ( application_user_name

  3  , created_by

  4  , creation_date

  5  , last_updated_by

  6  , last_update_date)

  7  VALUES

  8  ('Database Administrator'

  9  , 1

 10  , TRUNC(SYSDATE)

 11  , 1

 12  , TRUNC(SYSDATE));

The next statement creates the employee table:

SQL> CREATE TABLE employee

  2  ( employee_id       NUMBER

  3  , employee_number   VARCHAR2(10)

  4  , first_name        VARCHAR2(20) CONSTRAINT employee_nn1 NOT NULL

  5  , middle_name       VARCHAR2(20)

  6  , last_name         VARCHAR2(20) CONSTRAINT employee_nn2 NOT NULL

  7  , created_by        NUMBER       CONSTRAINT employee_nn3 NOT NULL

  8  , creation_date     DATE         CONSTRAINT employee_nn5 NOT NULL

  9  , last_updated_by   NUMBER       CONSTRAINT employee_nn6 NOT NULL

 10  , last_update_date  DATE         CONSTRAINT employee_nn7 NOT NULL

 11  , CONSTRAINT employee_pk  PRIMARY KEY (employee_id)

 12  , CONSTRAINT employee_fk1 FOREIGN KEY (created_by)

 13    REFERENCES application_user (application_user_id)

 14  , CONSTRAINT employee_fk2 FOREIGN KEY (last_updated_by)

 15    REFERENCES application_user (application_user_id));

You create the employee_seq sequence with this statement:

SQL> CREATE SEQUENCE employee_seq;

Next, you create a trigger to generate sequence values like you did for the application_user table:

SQL>

CREATE OR REPLACE TRIGGER employee_t1

  2    BEFORE INSERT ON employee

  3    FOR EACH ROW

  4  BEGIN

  5    /* Check for a empty image_id primary key column value,

  6      and assign the next sequence value when it is missing. */

  7    IF :new.employee_id IS NULL THEN

  8    SELECT employee_seq.NEXTVAL

  9    INTO  :new.employee_id

 10    FROM  dual;

 11    END IF;

 12  END;

 13  /

You have created the two tables for our non-critical trigger. The next section relies on the framework and integrates with it.

Non-critical Trigger

Before you create the logging trigger, you should test the concept of replacing a whitespace in a multipart last name with a hyphenated name. The following INSERT trigger fixes user input by replacing the whitespace with a hyphen. It doesn’t log the entry and some times you won’t log results for this type of trigger.

You create the employee_t2 trigger with the following:

SQL> l

  1  CREATE OR REPLACE TRIGGER employee_t2

  2    BEFORE INSERT ON employee

  3    FOR EACH ROW

  4    FOLLOWS employee_t1

  5    WHEN (REGEXP_LIKE(new.last_name,' '))

  6  BEGIN

  7    /* Substitute a dash for the white space. */

  8    :new.last_name := REGEXP_REPLACE(:new.last_name,' ','-',1,1);

  9  END;

 10  /

Line 4 designates that employee_t2 executes after employee_t1, which is the purpose of the FOLLOWS command. Line 8 uses the REGEXP_REPLACE function to find and replace the first instance of a whitespace with a hyphen.

After creating the employee_t2 trigger, you can test it by using an INSERT statement like this:

SQL> INSERT INTO employee

  2  ( employee_number

  3  , first_name

  4  , last_name

  5  , created_by

  6  , creation_date

  7  , last_updated_by

  8  , last_update_date )

  9  VALUES

 10  ('B12345-678'

 11  ,'Sandy'

 12  ,'Johnston Smith'

 13  , 1

 14  , TRUNC(SYSDATE)

 15  , 1

 16  , TRUNC(SYSDATE));

You can verify that the employee_t1 trigger prevented the entry of a multipart last name with the following query:

SQL> COLUMN employee_id     FORMAT 9999 HEADING "Employee|ID #"

SQL> COLUMN employee_number FORMAT A10  HEADING "Employee|Number"

SQL> COLUMN first_name      FORMAT A20  HEADING "First Name"

SQL> COLUMN last_name       FORMAT A20  HEADING "Last Name"

SQL> SELECT   employee_id

  2  ,        employee_number

  3  ,        first_name

  4  ,        last_name

  5  FROM     employee;

It returns:

Employee Employee

    ID # Number     First Name             Last Name

-------- ---------- -------------------- --------------------

       1 B12345-678 Sandy                Johnston-Smith

As you see from the results, the last name is hyphenated. If we accept another use case for the UPDATE statement, we may treat updates like you treat inserts.

An INSERT trigger doesn’t guarantee the user can’t change the hyphenated last name into a multipart last name. The application user can always change the value by using an UPDATE statement. That’s why there must be an UPDATE trigger.

The first element of a our

SQL> CREATE OR REPLACE TRIGGER employee_t3

  2    BEFORE UPDATE OF last_name ON employee

  3    FOR EACH ROW

  4    WHEN (REGEXP_LIKE(new.last_name,' '))

  5  BEGIN

  6    /* Substitute a dash for the white space. */

  7    :new.last_name := REGEXP_REPLACE(:new.last_name,' ','-',1,1);

  8  END;

  9  /

Line 2 guarantees that the UPDATE trigger only runs when an UPDATE statement changes the last_name column of the employee table. An UPDATE statement like the following causes the trigger to run (technically, the jargon is “fire”):

SQL> UPDATE   employee

  2  SET      last_name = 'Johnston Smith'

  3  WHERE    employee_number = 'B12345-678';

Having shown you how to create the non-critical INSERT and UPDATE triggers, I’ll now show you how to create the following employee_log table. This is where you can store the results from INSERT, UPDATE, and DELETE triggers. All columns are nullable (or optional) columns except the sequence generated employee_log_id column. The columns are optional because an INSERT statement never has an old set of values, and a DELETE statement never has a new set of values. Only the UPDATE statement provides old and new values inside a trigger.

The following creates the employee_log table:

SQL> CREATE TABLE employee_log

  2  ( employee_log_id         NUMBER

  3  , employee_event          VARCHAR2(6)

  4  , old_employee_id         NUMBER

  5  , old_employee_number     VARCHAR2(10)

  6  , old_first_name          VARCHAR2(20)

  7  , old_middle_name         VARCHAR2(20)

  8  , old_last_name           VARCHAR2(20)

  9  , old_created_by          NUMBER

 10  , old_creation_date       DATE

 11  , old_last_updated_by     NUMBER

 12  , old_last_update_date    DATE

 13  , new_employee_id         NUMBER

 14  , new_employee_number     VARCHAR2(10)

 15  , new_first_name          VARCHAR2(20)

 16  , new_middle_name         VARCHAR2(20)

 17  , new_last_name           VARCHAR2(20)

 18  , new_created_by          NUMBER

 19  , new_creation_date       DATE

 20  , new_last_updated_by     NUMBER

 21  , new_last_update_date    DATE

 22  , CONSTRAINT employee_log_pk  PRIMARY KEY (employee_log_id));

 You should create the employee_log_seq sequence, like

SQL> CREATE SEQUENCE employee_log_seq;

Then, you should add an employee_log_t1 trigger to generate the sequence value automatically. The trigger follows the pattern of the prior two triggers for the application_user and employee tables.

You create the employee_log_seq trigger with the following syntax:

SQL> CREATE OR REPLACE TRIGGER employee_log_t1

  2    BEFORE INSERT ON employee_log

  3    FOR EACH ROW

  4  BEGIN

  5    /* Check for a empty image_id primary key column value,

  6      and assign the next sequence value when it is missing. */

  7    IF :new.employee_log_id IS NULL THEN

  8    SELECT employee_log_seq.NEXTVAL

  9    INTO  :new.employee_log_id

 10    FROM  dual;

 11    END IF;

 12  END;

 13  /

The logging table is the first step. After creating the logging table, you need to create a standalone log_invalid_employee procedure. The following code creates the procedure. This procedure only runs in the current transaction context, and later another version shows you how to implement it in an autonomous transaction context.

SQL> CREATE OR REPLACE

  2    PROCEDURE log_invalid_employee

  3    ( pv_employee_event     VARCHAR2

  4    , pv_old_employee_id    NUMBER

  5    , pv_old_employee_number   VARCHAR2

  6    , pv_old_first_name     VARCHAR2

  7    , pv_old_last_name      VARCHAR2

  8    , pv_old_created_by     NUMBER

  9    , pv_old_creation_date         DATE

 10    , pv_old_last_updated_by   NUMBER

 11    , pv_old_last_update_date  DATE

 12    , pv_new_employee_id    NUMBER

 13    , pv_new_employee_number   VARCHAR2

 14    , pv_new_first_name     VARCHAR2

 15    , pv_new_last_name      VARCHAR2

 16    , pv_new_created_by     NUMBER

 17    , pv_new_creation_date         DATE

 18    , pv_new_last_updated_by   NUMBER

 19    , pv_new_last_update_date  DATE) IS

 20  BEGIN

 21    /* Write to the log table. */

 22    INSERT INTO employee_log

 23    ( employee_event

 24    , old_employee_id

 25    , old_employee_number

 26    , old_first_name

 27    , old_last_name

 28    , old_created_by

 29    , old_creation_date

 30    , old_last_updated_by

 31    , old_last_update_date

 32    , new_employee_id

 33    , new_employee_number

 34    , new_first_name

 35    , new_last_name

 36    , new_created_by

 37    , new_creation_date

 38    , new_last_updated_by

 39    , new_last_update_date )

 40    VALUES

 41    ( pv_employee_event

 42    , pv_old_employee_id

 43    , pv_old_employee_number

 44    , pv_old_first_name

 45    , pv_old_last_name

 46    , pv_old_created_by

 47    , pv_old_creation_date

 48    , pv_old_last_updated_by

 49    , pv_old_last_update_date

 50    , pv_new_employee_id

 51    , pv_new_employee_number

 52    , pv_new_first_name

 53    , pv_new_last_name

 54    , pv_new_created_by

 55    , pv_new_creation_date

 56    , pv_new_last_updated_by

 57    , pv_new_last_update_date );

 58  END log_invalid_employee;

 59  /

With the logging table and procedure, you can now rework the INSERT and UPDATE triggers into a single trigger. The new trigger fires when an INSERT or an UPDATE statement affects the employee table. That means you can log the data from both events.

If you created employee_t1, employee_t2 and employee_t3 triggers, you need to drop employee_t2 and employee_t3 triggers before creating the new trigger. The previous employee_t3  trigger will cause incorrect behaviors because it is incompatible with the new employee_t1 trigger.

The new employee_t1 trigger is:

SQL> CREATE OR REPLACE TRIGGER employee_t1

  2    BEFORE INSERT OR UPDATE OF last_name ON employee

  3    FOR EACH ROW

  4    WHEN (REGEXP_LIKE(new.last_name,' '))

  5  DECLARE

  6    /* DML event label. */

  7    lv_employee_event  VARCHAR2(6);

  8  BEGIN

  9    /* Check for an event and assign event value. */

 10    IF INSERTING THEN

 11    /* Check for a empty image_id primary key column value,

 12        and assign the next sequence value when it is missing. */

 13    IF :new.employee_id IS NULL THEN

 14       SELECT employee_seq.NEXTVAL

 15       INTO   :new.employee_id

 16       FROM   dual;

 17    END IF;

 18    lv_employee_event := 'INSERT';

 19    ELSE

 20    lv_employee_event := 'UPDATE';

 21    END IF;

 22

 23    /* Log the details captured by an insert or update. */

 24    log_invalid_employee

 25    ( pv_employee_event => lv_employee_event

 26    , pv_old_employee_id => :old.employee_id

 27    , pv_old_employee_number => :old.employee_number

 28    , pv_old_first_name => :old.first_name

 29    , pv_old_last_name => :old.last_name

 30    , pv_old_created_by => :old.created_by

 31    , pv_old_creation_date => :old.creation_date

 32    , pv_old_last_updated_by => :old.last_updated_by

 33    , pv_old_last_update_date => :old.last_update_date

 34    , pv_new_employee_id => :new.employee_id

 35    , pv_new_employee_number => :new.employee_number

 36    , pv_new_first_name => :new.first_name

 37    , pv_new_last_name => :new.last_name

 38    , pv_new_created_by => :new.created_by

 39    , pv_new_creation_date => :new.creation_date

 40    , pv_new_last_updated_by => :new.last_updated_by

 41    , pv_new_last_update_date => :new.last_update_date );

 42

 43    /* Substitute a dash for the white space. */

 44    :new.last_name := REGEXP_REPLACE(:new.last_name,' ','-',1,1);

 45  END;

 46  /

This non-critical trigger checks whether the event is an INSERT statement on line 10.  The trigger generates a sequence value when an INSERT statement fires the trigger. Then, the trigger sets a local variable with the INSERT string. It assigns an UPDATE string when an UPDATE statement fires the trigger.

After the event detection logic, the trigger calls the log_invalid_employee procedure on line 24. Line 44 changes the multipart last name into a hyphenated last name.

This part of the article has shown you how to create and manage non-critical triggers.

Critical Trigger

This part of the article shows you how to create and manage critical triggers. The key difference is that critical triggers stop the transaction that fires the trigger. This has significant impact on how you design and implement the log_invalid_employee procedure.

You need to modify the log_invalid_employee procedure so that it supports autonomous transactions. That requires adding a PRAGMA precompiler directive in the declaration block and a COMMIT statement after the INSERT statement.

The following shows you the changes required in the log_invalid_employee procedure:

SQL> CREATE OR REPLACE

  2    PROCEDURE log_invalid_employee

  3    ( pv_employee_event         VARCHAR2

  4    , pv_old_employee_id        NUMBER

 ...

 21    /* Set precompiler directive to run in a separate context. */

 22    PRAGMA AUTONOMOUS_TRANSACTION;

 23  BEGIN

 ...

 62    /* Commit the autonmous transaction. */

 63    COMMIT;

 64  END log_invalid_employee;

 65  /

Line 22 holds the autonomous transaction PRAGMA, and line 63 holds the COMMIT statement. Both of these are required when you want to enable a trigger to both log data and raise an exception that terminates the transaction.

Next, you need to rework the employee_t1 trigger by adding content to the declaration and execution blocks, and by adding an exception block. The declaration block requires you to declare an exception variable and create a PRAGMA precompiler directive. The exception block requires you to add a conditional block at the end of the execution block. You also need to add an exception block to manage a raised exception.

The following shows you the changes required for the employee_t1 trigger:

SQL> CREATE OR REPLACE TRIGGER employee_t1

  2    BEFORE INSERT OR UPDATE OF last_name ON employee

  3    FOR EACH ROW

  4    WHEN (REGEXP_LIKE(new.last_name,' '))

  5  DECLARE

 ...

  9    /* Declare exception. */

 10    e EXCEPTION;

 11    PRAGMA EXCEPTION_INIT(e,-20001);

 12  BEGIN

 ...

 47    /* Re-check for an event and assign event value. */

 48    IF INSERTING THEN

 49      /* Substitute a dash for the white space. */

 50      :new.last_name := REGEXP_REPLACE(:new.last_name,' ','-',1,1);

 51    ELSE

 52      /* Throw exception. */

 53      RAISE_APPLICATION_ERROR(-20001,'No two-part last names without a hyphen.');

 54    END IF;

 55  EXCEPTION

 56    /* Capture an exception. */

 57    WHEN e THEN

 58     ROLLBACK;

 59    dbms_output.put_line('[Trigger Event: '||lv_employee_event||']');

 60    dbms_output.put_line(SQLERRM);

 61    WHEN others THEN

 62    dbms_output.put_line(SQLERRM);

 63  END;

 64  /

Line 10 declares a local exception variable. Line 11 declares the PRAGMA precompiler directive. Lines 47 through 54 implements the conditional logic for writing a hyphenated last name for an INSERT statement, and the logic for raising an exception for an UPDATE statement.

An INSERT statement causes the database trigger to write to the employee_log logging table. An UPDATE statement causes the database trigger to write to the logging table and throw an exception.

The test case for a critical event trigger uses an UPDATE statement, as shown:

SQL> UPDATE   employee

  2  SET      last_name = 'Zeta Jones'

  3  WHERE    employee_number = 'B98765-678';

The first thing you see is a thrown exception, like

[Trigger Event: UPDATE]

ORA-20001: No two-part last names without a hyphen.

 

After you see the thrown exception, you can run the following query to see what has been written to the exception_log table:

SQL> COLUMN employee_log_id  FORMAT 9999 HEADING "Empl|Log|ID #"

SQL> COLUMN old_employee_id      FORMAT 9999 HEADING "Empl|ID #"

SQL> COLUMN old_name             FORMAT A25  HEADING "Old Name"

SQL> COLUMN new_employee_id      FORMAT 9999 HEADING "Empl|ID #"

SQL> COLUMN new_name             FORMAT A25  HEADING "New Name"

SQL> SELECT   employee_log_id

  2  ,        old_employee_id

  3  ,        DECODE( old_last_name || ', '|| old_first_name,', ',NULL

  4                 , old_last_name || ', '|| old_first_name) AS "old_name"

  5  ,        new_employee_id

  6  ,        DECODE( new_last_name || ', '|| new_first_name,', ',NULL

  7                 , new_last_name || ', '|| new_first_name) AS "new_name"

  8  FROM     employee_log;

It displays:

 Empl

  Log  Empl                            Empl

 ID #  ID # Old Name                   ID # New Name

----- ----- ------------------------- ----- -------------------------

    1                                     2 Evert Lloyd, Chris

    2     2 Evert-Lloyd, Chris            2 Evert Lloyd, Chris

    3                                     3 Zeta Jones, Catherine

    4     3 Zeta-Jones, Catherine         3 Zeta Jones, Catherine

The ultimate test of these is that while there were many attempts at entering a multipart last name, none of them succeeds. You can query the last_name column from the employee table to verify that, like

SQL> SELECT   last_name

  2  FROM     employee;

It should show you the three rows that you’ve inserted and updated through this article. You should see:

Last Name

-------------------------

Johnston-Smith

Evert-Lloyd

Zeta-Jones

Through this article you should have learned how to create non-critical and critical triggers. These techniques are important when you manage transactions against business rules that can’t be supported by ordinary database constraints.