MacLochlainns Weblog

Designing and implementing database triggers is always interesting and sometimes not easy. I believe most of the difficulty comes from not implementing the triggers in a way that lets you perform single use case testing. For example, a trigger typically fires as a result of an INSERT, UPDATE, or DELETE statement. That means you can’t test the trigger’s logic independently from the SQL statement.

This post shows you how to implement an Oracle Database trigger that ensures a last_name field always has a hyphen when it is composed of two surnames. It also shows you how to build debugging directly into the trigger with Oracle’s conditional compilation logic (covered in my Oracle Database 12c PL/SQL Programming book on pages 170-171) while writing the debug comments to a debug logging table.

The example works through the design in stages. To begin the process, you need to define a zeta table and zeta_s sequence (no magic in the table or sequence names).

-- Create the zeta demo table.
CREATE TABLE zeta
( zeta_id     NUMBER
, last_name   VARCHAR2(30));
 
-- Create the zeta_s demo sequence.
CREATE SEQUENCE zeta_s;

Next, you write a basic on insert row-level (or, row-by-row) trigger. The following white_space trigger only fires when the last_name column value contains a whitespace between two components of a last name.

The code follows below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

/*
|| Create an on insert trigger to implement the desired
|| logic, which replaces a whitespace between two portions
|| of a last_name column.
*/
CREATE OR REPLACE
  TRIGGER white_space
  BEFORE INSERT ON zeta
  FOR EACH ROW
  WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END white_space;
/

You can now test the white_space trigger with these two INSERT statements:

-- Two test insert statements.
INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Baron-Schwartz' );
 
INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Zeta Jones' );

After running the two INSERT statements, you can query the last_name from the zeta table and verify that there’s always a hyphen between the two components of the last name, like:

SELECT * FROM zeta;

It should display:

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta-Jones

However, the business logic is violated when you run an UPDATE statement, like:

-- Update data and break the business rule.
UPDATE zeta
SET    last_name = 'Zeta Jones'
WHERE  last_name = 'Zeta-Jones';

A fresh query like

SELECT * FROM zeta;

Should display the following, which allowed an UPDATE statement to put in a non-conforming last name value:

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta Jones

You need to expand the role of your white_space trigger to prevent this undesired outcome by enabling it to fire on an insert or update event. You do that by adding ON UPDATE to line 8 below. The modified white_space trigger for both SQL events is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

/*
|| Create an on insert or update trigger to implement the
|| desired logic, which replaces a whitespace between two 
|| portions of a last_name column.
*/
CREATE OR REPLACE
  TRIGGER white_space
  BEFORE INSERT OR UPDATE ON zeta
  FOR EACH ROW
  WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END white_space;
/

Having made the change on line 8 above, you can now retest the white_space trigger with the following UPDATE statement. You should note that the WHERE clause uses a whitespace because the last UPDATE statement with the INSERT-only white_space trigger allowed its change.

UPDATE zeta
SET    last_name = 'Zeta-Jones'
WHERE  last_name = 'Zeta Jones';

Re-query the zeta table:

SELECT * FROM zeta;

It should display the following values that meet the business rule:

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta Jones

The modified white_space trigger doesn’t let us capture debug information and it doesn’t let us see whether the SQL event is an INSERT or UPDATE statement. It also fails to differentiate between outcomes from an INSERT and UPDATE event.

You can fix this by:

• Creating a debug_log table that captures debugging information.
• Creating a debug_procedure to format diagnostic strings.
• Using the Data Manipulation Language (DML) Event Functions (covered in my Oracle Database 12c PL/SQL Programming book’s Table 12-3 on page 533) to track whether the event is an INSERT or UPDATE statement.

The three steps to make the trigger capable of different outcomes and debugging are:

1. The following creates a debug_log table:

   -- Create the debug_log table. CREATE TABLE debug_log ( message VARCHAR2(78));

   -- Create the debug_log table. CREATE TABLE debug_log ( message VARCHAR2(78));

2. The following creates an a debug procedure:

   -- Create a debug logging procedure. CREATE OR REPLACE PROCEDURE debug ( event VARCHAR2 := 'Unknown' , location VARCHAR2 , COLUMN VARCHAR2 ) IS /* Local message variable. */ lv_message VARCHAR2(78);   /* Set procedure as an autonomous transaction. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* Build, insert, and commit message in log. */ lv_message := event || ' event at ' || location || ' on column [' || COLUMN || ']'; INSERT INTO debug_log ( message ) VALUES ( lv_message ); COMMIT; END; /

   -- Create a debug logging procedure. CREATE OR REPLACE PROCEDURE debug ( event VARCHAR2 := 'Unknown' , location VARCHAR2 , column VARCHAR2 ) IS /* Local message variable. */ lv_message VARCHAR2(78); /* Set procedure as an autonomous transaction. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* Build, insert, and commit message in log. */ lv_message := event || ' event at ' || location || ' on column [' || column || ']'; INSERT INTO debug_log ( message ) VALUES ( lv_message ); COMMIT; END; /

3. The following creates an a replacement white_space trigger equipped with event tracking and conditional compilation debug calls to the debug_log table:

   You actually need to change the session before compiling this trigger with the following command so that the conditional compilation instructions work:

   ALTER SESSION SET PLSQL_CCFLAGS = 'DEBUG:1';

   ALTER SESSION SET PLSQL_CCFLAGS = 'DEBUG:1';

   Then, create the white_space trigger from the following code:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

   -- Create a debug logging procedure. CREATE OR REPLACE TRIGGER white_space BEFORE INSERT OR UPDATE ON zeta FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) DECLARE lv_event VARCHAR2(9); BEGIN /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'before IF statement' , column_value => ':new.last_name' ); $END   IF INSERTING THEN lv_event := 'Inserting';   /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => lv_event , location => 'after IF statement' , column_value => ':new.last_name' ); $END   :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); ELSIF UPDATING THEN lv_event := 'Updating';   /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => lv_event , location => 'after ELSIF statement' , column_value => ':new.last_name' ); $END   RAISE_APPLICATION_ERROR(-20001,'Whitespace replaced with hyphen.'); END IF; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'after END IF statement' , column_value => ':new.last_name' ); $END END white_space; /

   -- Create a debug logging procedure. CREATE OR REPLACE TRIGGER white_space BEFORE INSERT OR UPDATE ON zeta FOR EACH ROW WHEN (REGEXP_LIKE(new.last_name,' ')) DECLARE lv_event VARCHAR2(9); BEGIN /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'before IF statement' , column_value => ':new.last_name' ); $END IF INSERTING THEN lv_event := 'Inserting'; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => lv_event , location => 'after IF statement' , column_value => ':new.last_name' ); $END :new.last_name := REGEXP_REPLACE(:new.last_name,' ','-',1,1); ELSIF UPDATING THEN lv_event := 'Updating'; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => lv_event , location => 'after ELSIF statement' , column_value => ':new.last_name' ); $END RAISE_APPLICATION_ERROR(-20001,'Whitespace replaced with hyphen.'); END IF; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'after END IF statement' , column_value => ':new.last_name' ); $END END white_space; /

A new test case for the modified white_space trigger uses an INSERT and UPDATE statement, like:

INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Pinkett Smith' );
 
UPDATE zeta
SET    last_name = 'Pinkett Smith'
WHERE  last_name = 'Pinkett-Smith';

The UPDATE statement violates the business rule and the new white_space trigger throws an error when an attempt is made to update the last_name with two names separated by a whitespace. The UPDATE statement raises the following error stack:

UPDATE zeta
       *
ERROR AT line 1:
ORA-20001: Whitespace replaced WITH hyphen.
ORA-06512: AT "STUDENT.WHITE_SPACE", line 31
ORA-04088: error during execution OF TRIGGER 'STUDENT.WHITE_SPACE'

Re-query the zeta table:

SELECT * FROM zeta;

It should display the following values that meet the business rule. The new third row in the table came from the INSERT statement in the test case.

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta-Jones
         3 Pinkett-Smith

Unfortunately, there’s a lot of debugging clutter in the white_space trigger. The other downside is it requires testing from INSERT and UPDATE statements rather than simple anonymous block. You can fix that by doing two things:

1. Remove the body of the trigger to an autonomous zeta_function.
2. Put a logic router in the trigger with a call to the autonomous zeta_function.

Here’s the script to create the zeta_function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

CREATE OR REPLACE
  FUNCTION zeta_function 
  ( column_value  VARCHAR2
  , event         VARCHAR2 ) RETURN VARCHAR2 IS
 
    /* Return value. */
    lv_retval  VARCHAR2(30) := column_value;
 
    /* Set function as an autonomous transaction. */ 
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
 
    /* Conditional debugging. */
    $IF $$DEBUG = 1 $THEN
      debug( location     => 'before IF statement'
           , column_value => ':new.column_value' );
    $END
 
    /* Check if event is INSERT statement. */
    IF event = 'INSERTING' THEN
 
      /* Conditional debugging. */
      $IF $$DEBUG = 1 $THEN
        debug( event        =>  INITCAP(event)
             , location     => 'after IF statement'
             , column_value => ':new.column_value' );
      $END
 
      /* Replace a whitespace with a hyphen. */
      lv_retval := REGEXP_REPLACE(column_value,' ','-',1,1);
 
    /* Check if event is UPDATE statement. */
    ELSIF event = 'UPDATING' THEN
 
      /* Conditional debugging. */
      $IF $$DEBUG = 1 $THEN
        debug( event        =>  INITCAP(event)
             , location     => 'after ELSIF statement'
             , column_value => ':new.column_value' );
      $END
 
      /* Raise error to state policy allows no changes. */
      RAISE_APPLICATION_ERROR(-20001,'Whitespace replaced with hyphen.');
 
    END IF;
 
      /* Conditional debugging. */
      $IF $$DEBUG = 1 $THEN
        debug( location     => 'after END IF statement'
             , column_value => ':new.column_value' );
      $END
 
    /* Return modified column for insert or original column for update. */
    RETURN lv_retval;
  END zeta_function;
/

The refactored white_space trigger follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE OR REPLACE
  TRIGGER white_space
  BEFORE INSERT OR UPDATE ON zeta
  FOR EACH ROW
  WHEN (REGEXP_LIKE(NEW.last_name,' '))
DECLARE
  lv_event  VARCHAR2(9);
BEGIN
  /* Set evaluation event. */
  IF    INSERTING THEN lv_event := 'INSERTING';
  ELSIF UPDATING  THEN lv_event := 'UPDATING';
  END IF;
 
  /*
  || Assign the result of the formatted string to the 
  || new last_name value.
  */
  :NEW.last_name := zeta_function( event        => lv_event
                                 , column_value => :NEW.last_name);
END white_space;
/

A new test case for the modified white_space trigger uses an INSERT and UPDATE statement with some new values.

INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Day Lewis' );
 
UPDATE zeta
SET    last_name = 'Day Lewis'
WHERE  last_name = 'Day-Lewis';

The UPDATE statement continues to violate the business rule and the modified white_space trigger throws a different error stack. The new error stack includes the zeta_function because that’s where you throw the error. It is caught and re-thrown by the white_space trigger.

UPDATE zeta
       *
ERROR AT line 1:
ORA-20001: Whitespace replaced WITH hyphen.
ORA-06512: AT "STUDENT.ZETA_FUNCTION", line 47
ORA-06512: AT "STUDENT.WHITE_SPACE", line 13
ORA-04088: error during execution OF TRIGGER 'STUDENT.WHITE_SPACE'

Re-query the zeta table:

SELECT * FROM zeta;

It should display the following values that meet the business rule. The new third row in the table came from the INSERT statement in the test case.

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta-Jones
         3 Pinkett-Smith
         4 Day-Lewis

Now, you can query the debug_log table and see the debug messages that you captured from testing the INSERT and UPDATE statements. You get three messages from the INSERT statement test and only two from the UPDATE statement test.

MESSAGE
------------------------------------------------------------------
Unknown event at before IF statement on column [:new.last_name]
Inserting event at after IF statement on column [:new.last_name]
Unknown event at after END IF statement on column [:new.last_name]
Unknown event at before IF statement on column [:new.last_name]
Updating event at after ELSIF statement on column [:new.last_name]

As always, I hope this helps people see new ways to solve problems.