MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Logging Triggers

without comments

Oracle Logging Trigger Results

This article demonstrates how you can write log files from triggers on different tables to the same logging table. This approach leverages Oracle’s object types and column substitutability features. It also eliminates the requirement to create a unique logging table for each logging trigger. The trick to accomplishing this requires mastering two skills.

The first skill requires you to learn how to create user-defined types (UDTs) and subtypes. The UDT stores the elements common to all logging data, and the UDT subtype stores the unique column values of individual tables. The second skill requires you to learn how to create a logging table that uses a base UDT as a column type, and to learn how to insert new data into and query subtype data from a UDT subtype.

You will learn both skills in this article. If you’re new to database triggers and Oracle’s object types, I’d recommend you check out my earlier “Critical and Non-critical Triggers” and “Object Types and Column Substitutability” articles.

The article works through the steps in four parts. You create:

  1. Five tables and sequences, and one UDT base type and two subtypes that map to the specific tables
  2. A reusable autonomous stored procedure
  3. Two data manipulation language (DML) triggers
  4. A test case with standalone PL/SQL blocks that query the data

This article uses small headers to organize the parts. After creating and testing the parts, there are some observations and suggestions at the end of the article.

Creating Tables, Sequences, and Types

You create five tables because of foreign key dependencies. The application_user table supports the use of who-audit columns. Who-audit columns document the user who creates and last updates every row of data. Who-audit columns must link to an access control list (ACL), which are typically a list of user names and their encrypted password keys.

The following creates the ACL table and sequence:

SQL> CREATE TABLE application_user
  2  ( application_user_id    NUMBER       CONSTRAINT app_user_pk PRIMARY KEY
  3  , application_user_name  VARCHAR2(30) CONSTRAINT app_user_nn1 NOT NULL
  4  , created_by             NUMBER       CONSTRAINT app_user_nn2 NOT NULL
  5  , creation_date          DATE         CONSTRAINT app_user_nn3 NOT NULL
  6  , last_updated_by        NUMBER       CONSTRAINT app_user_nn4 NOT NULL
  7  , last_update_date       DATE         CONSTRAINT app_user_nn5 NOT NULL
  8  , CONSTRAINT app_user_fk1 FOREIGN KEY(created_by)
  9    REFERENCES application_user(app_user_id)
 10  , CONSTRAINT app_user_fk2 FOREIGN KEY(last_updated_by)
 11    REFERENCES application_user(app_user_id));
SQL> CREATE SEQUENCE application_user_seq;

After you create the application_user table and application_user_seq sequence, you need to insert one row. The row let’s you validate the created_by and last_updated_by who-audit columns.

The following creates the mpaa table and mpaa_seq sequence:

SQL> CREATE TABLE mpaa
  2  ( mpaa_id           NUMBER         CONSTRAINT mpaa_pk  PRIMARY KEY
  3  , rating_code       VARCHAR2(5)    CONSTRAINT mpaa_nn1 NOT NULL
  4  , rating_name       VARCHAR2(30)   CONSTRAINT mpaa_nn2 NOT NULL
  5  , rating_desc       VARCHAR2(180)  CONSTRAINT mpaa_nn3 NOT NULL
  6  , created_by        NUMBER         CONSTRAINT mpaa_nn4 NOT NULL
  7  , creation_date     DATE           CONSTRAINT mpaa_nn5 NOT NULL
  8  , last_updated_by   NUMBER         CONSTRAINT mpaa_nn6 NOT NULL
  9  , last_update_date  DATE           CONSTRAINT mpaa_nn7 NOT NULL
 10  , CONSTRAINT mpaa_fk1 FOREIGN KEY(created_by)
 11    REFERENCES application_user(application_user_id)
 12  , CONSTRAINT mpaa_fk2 FOREIGN KEY(last_updated_by)
 13    REFERENCES application_user(application_user_id));
SQL> CREATE SEQUENCE mpaa_seq;

The mpaa table supports film ratings for the film table. The film table’s mpaa_id column holds foreign key values that reference the mpaa table. The film and employee tables are the principle testing tables for the stored procedure, triggers, and trigger event logging.

The following creates the film table and film_seq sequence:

SQL> CREATE TABLE film
  2  ( film_id           NUMBER        CONSTRAINT film_pk PRIMARY KEY
  3  , film_name         VARCHAR2(40)  CONSTRAINT film_nn1 NOT NULL
  4  , release_date      DATE          CONSTRAINT film_nn2 NOT NULL
  5  , mpaa_id           NUMBER        CONSTRAINT film_nn3 NOT NULL
  6  , created_by        NUMBER        CONSTRAINT film_nn4 NOT NULL
  7  , creation_date     DATE          CONSTRAINT film_nn5 NOT NULL
  8  , last_updated_by   NUMBER        CONSTRAINT film_nn6 NOT NULL
  9  , last_update_date  DATE          CONSTRAINT film_nn7 NOT NULL
 10  , CONSTRAINT film_fk1 FOREIGN KEY(created_by)
 11    REFERENCES application_user(application_user_id)
 12  , CONSTRAINT film_fk2 FOREIGN KEY(last_updated_by)
 13    REFERENCES application_user(application_user_id)
 14  , CONSTRAINT film_fk3 FOREIGN KEY (mpaa_id)
 15    REFERENCES mpaa (mpaa_id));
SQL> CREATE SEQUENCE film_seq;

The following creates the employee table and employee_seq sequence:

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));
SQL> DROP SEQUENCE employee_seq;

You should populate some data in the application_user, mpaa, film, and employee tables. This testing ensures the interdependencies work.

Before you create the trigger_log table, you need to create three UDTs. The base_t object type requires you create a base_t object type and implement a base_t object body.

The following creates the base_t object type:

SQL> CREATE OR REPLACE
  2    TYPE base_t IS OBJECT
  3    ( oname  VARCHAR2(30)
  4    , CONSTRUCTOR FUNCTION base_t
  5      RETURN SELF AS RESULT
  6    , MEMBER FUNCTION get_oname RETURN VARCHAR2
  7    , MEMBER PROCEDURE set_oname (oname VARCHAR2)
  8    , MEMBER FUNCTION to_string RETURN VARCHAR2)
  9    INSTANTIABLE NOT FINAL;
 10  /

The following creates the base_t object body:

SQL> CREATE OR REPLACE
  2    TYPE BODY base_t IS
  3    /* A default constructor w/o formal parameters. */
  4    CONSTRUCTOR FUNCTION base_t
  5    RETURN SELF AS RESULT IS
  6      BEGIN
  7        self.oname := 'BASE_T';
  8        RETURN;
  9      END;
 10    /* An accessor, or getter, method. */
 11    MEMBER FUNCTION get_oname RETURN VARCHAR2 IS
 12      BEGIN
 13        RETURN self.oname;
 14      END get_oname;
 15    /* A mutator, or setter, method. */
 16    MEMBER PROCEDURE set_oname
 17    ( oname  VARCHAR2 ) IS
 18      BEGIN
 19        self.oname := oname;
 20      END set_oname;
 21    /* A to_string conversion method. */
 22    MEMBER FUNCTION to_string RETURN VARCHAR2 IS
 23      BEGIN
 24        RETURN self.oname;
 25      END to_string;
 26  END;
 27  /

Lines 4 through 9 implements a no-argument constructor that automatically assigns a literal value to the oname field on line 7. This type of constructor lets you create an instance of the base_t object type without providing an oname. Lines 11 through 14 implements a getter for the oname field, and lines 16 through 20 implements a setter for the oname field.

Lines 22 through 26 implements a to_string function that prints the oname field value. The to_string function also provides a convenient way to test the object type of object instance stored in tables, as you will see later in this article.

You can now create the UDT subtypes for the employee and film tables. The base_t name represents the base type or a super type. The subtypes for the employee and film tables use the more conventional _obj suffix.

The following creates the employee_obj UDT subtype:

SQL> CREATE OR REPLACE
  2    TYPE employee_obj UNDER base_t
  3    ( employee_id      NUMBER
  4    , employee_number  VARCHAR2(10)
  5    , first_name       VARCHAR2(20)
  6    , middle_name      VARCHAR2(20)
  7    , last_name        VARCHAR2(20)
  8    , created_by        NUMBER
  9    , creation_date     DATE
 10    , last_updated_by   NUMBER
 11    , last_update_date  DATE);
 12  /

The following creates the film_obj UDT subtype:

SQL> CREATE OR REPLACE
  2    TYPE film_obj UNDER base_t
  3    ( film_id           NUMBER
  4    , film_name         VARCHAR2(40)
  5    , release_date      DATE
  6    , mpaa_id           NUMBER
  7    , created_by        NUMBER
  8    , creation_date     DATE
  9    , last_updated_by   NUMBER
 10    , last_update_date  DATE);
 11    /

After creating the base_t UDT and the employee_obj and film_obj subtypes, you can create the trigger_log table. The following creates the trigger_log table and trigger_log_s sequence:

SQL> CREATE TABLE trigger_log
  2  ( trigger_log_id      NUMBER
  3  , table_name          VARCHAR2(30)
  4  , trigger_event       VARCHAR2(6)
  5  , transaction_status  VARCHAR2(9)
  6  , old_instance        BASE_T
  7  , new_instance        BASE_T );

The surrogate key for the table is the trigger_log_id column. The composite key of the table_name, trigger_event, and transaction_status columns define the natural key for table. The old_instance and new_instance columns hold respectively the values for any table before and after the DML event.

Autonomous Procedure

You have a 32,000-byte limit on the size of database triggers. Also, you have a limit on the scope of database triggers. A database trigger must run in the same context as the DML event, which means a trigger can’t write a log file when it raises an exception. You can write a log file when the trigger raises an exception by calling a procedure that runs as an anonymous transaction.

The following implements anonymous-transaction procedure:

SQL> CREATE OR REPLACE
  2    PROCEDURE log_trigger_result
  3    ( pv_table_name          VARCHAR2
  4    , pv_trigger_event       VARCHAR2
  5    , pv_transaction_status  VARCHAR2
  6    , pv_old_instance        BASE_T
  7    , pv_new_instance        BASE_T ) IS
  8
  9    /* Set precompiler directive to run in a separate context. */
 10    PRAGMA AUTONOMOUS_TRANSACTION;
 11  BEGIN
 12    /* Write to the log table. */
 13    INSERT INTO trigger_log
 14    ( trigger_log_id
 15    , table_name
 16    , trigger_event
 17    , transaction_status
 18    , old_instance
 19    , new_instance )
 20    VALUES
 21    ( trigger_log_s.NEXTVAL
 22    , pv_table_name
 23    , pv_trigger_event
 24    , pv_transaction_status
 25    , pv_old_instance
 26    , pv_new_instance );
 27
 28    /* Commit the autonmous transaction. */
 29    COMMIT;
 30  END log_trigger_result;
 31  /

Lines 6 and 7 uses a base_t UDT as a parameter type, which means it accepts a base_t type or any subtype. Line 10 set a pre-compiler directive that enables the log_trigger_result procedure to run in an independent thread of execution.

Autonomous Procedure

The INSERT statement designates two base_t columns on lines 18 and 19, and then it passes the two base_t parameters in the VALUES clause. Line 29 commits the record into the trigger_log table.

The following implements an INSERT or UPDATE event trigger on the employee table:

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    lv_transaction_status  VARCHAR2(9) := 'REJECTED';
  9
 10    /* Declare exception. */
 11    e EXCEPTION;
 12    PRAGMA EXCEPTION_INIT(e,-20001);
 13  BEGIN
 14    /* Check for an event and assign event value. */
 15    IF INSERTING THEN
 16      /* Check for a empty image_id primary key column value,
 17         and assign the next sequence value when it is missing. */
 18      IF :NEW.employee_id IS NULL THEN
 19        SELECT employee_seq.NEXTVAL
 20        INTO   :NEW.employee_id
 21        FROM   dual;
 22      END IF;
 23      :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
 24      lv_transaction_status := 'PROCESSED';
 25      lv_employee_event := 'INSERT';
 26    ELSE
 27      lv_employee_event := 'UPDATE';
 28    END IF;
 29
 30    /* Log the details captured by an insert or update. */
 31    log_trigger_result
 32    ( pv_table_name => 'EMPLOYEE'
 33    , pv_trigger_event => lv_employee_event
 34    , pv_transaction_status => lv_transaction_status
 35    , pv_new_instance =>
 36        employee_obj(
 37          oname => 'EMPLOYEE_OBJ'
 38        , employee_id => :old.employee_id
 39        , employee_number => :old.employee_number
 40        , first_name => :old.first_name
 41        , middle_name => :old.middle_name
 42        , last_name => :old.last_name
 43        , created_by => :old.created_by
 44        , creation_date => :old.creation_date
 45        , last_updated_by => :old.last_updated_by
 46        , last_update_date => :old.last_update_date )
 47    , pv_old_instance =>
 48        employee_obj(
 49          oname => 'EMPLOYEE_OBJ'
 50        , employee_id => :NEW.employee_id
 51        , employee_number => :NEW.employee_number
 52        , first_name => :NEW.first_name
 53        , middle_name => :NEW.middle_name
 54        , last_name => :NEW.last_name
 55        , created_by => :NEW.created_by
 56        , creation_date => :NEW.creation_date
 57        , last_updated_by => :NEW.last_updated_by
 58        , last_update_date => :NEW.last_update_date ));
 59
 60    /* Throw exception. */
 61    IF UPDATING THEN
 62      RAISE_APPLICATION_ERROR(-20001
 63        ,'No two-part last names without a hyphen.');
 64    END IF;
 65
 66  EXCEPTION
 67    /* Capture an exception. */
 68    WHEN e THEN
 69      ROLLBACK;
 70      dbms_output.put_line('[Trigger Event: '||lv_employee_event||']');
 71      dbms_output.put_line(SQLERRM);
 72    WHEN OTHERS THEN
 73      dbms_output.put_line(SQLERRM);
 74  END;
 75  /

Line 8 sets the lv_transaction_status to REJECTED by default. Line 15 checks for an INSERT statement as the triggering event. It sets the lv_transaction_status to PROCESSED and sets the lv_employee_event to INSERT on lines 24 and 25. An UPDATE statement sets the lv_employee_event variable to UPDATE on line 27.

Lines 35 through 46 create an instance of the employee_obj as the old part of the INSERT statement. It should always be a null value for an INSERT statement. Lines 47 through 58 create an instance of the employee_obj as the new part of the INSERT statement.

The following implements an INSERT or UPDATE event trigger on the film table:

SQL> CREATE OR REPLACE TRIGGER film_t1
  2    BEFORE INSERT OR UPDATE OF film_name ON film
  3    FOR EACH ROW
  4  DECLARE
  5    /* DML event label. */
  6    lv_trigger_event  VARCHAR2(6);
  7    lv_transaction_status  VARCHAR2(9) := 'REJECTED';
  8
  9    /* Declare exception. */
 10    e EXCEPTION;
 11    PRAGMA EXCEPTION_INIT(e,-20001);
 12  BEGIN
 13    /* Check for an event and assign event value. */
 14    IF INSERTING THEN
 15      /* Check for a empty image_id primary key column value,
 16         and assign the next sequence value when it is missing. */
 17      IF :NEW.film_id IS NULL THEN
 18        SELECT film_seq.NEXTVAL
 19        INTO   :NEW.film_id
 20        FROM   dual;
 21      END IF;
 22      lv_trigger_event := 'INSERT';
 23      lv_transaction_status := 'PROCESSED';
 24    ELSIF UPDATING THEN
 25      lv_trigger_event := 'UPDATE';
 26    END IF;
 27
 28    /* Log the details captured by an insert or update. */
 29    log_trigger_result
 30    ( pv_table_name => 'FILM'
 31    , pv_trigger_event => lv_trigger_event
 32    , pv_transaction_status => lv_transaction_status
 33    , pv_new_instance =>
 34        film_obj(
 35          oname => 'FILM_OBJ'
 36        , film_id => :old.film_id
 37        , film_name => :old.film_name
 38        , release_date => :old.release_date
 39        , mpaa_id => :old.mpaa_id
 40        , created_by => :old.created_by
 41        , creation_date => :old.creation_date
 42        , last_updated_by => :old.last_updated_by
 43        , last_update_date => :old.last_update_date )
 44    , pv_old_instance =>
 45        film_obj(
 46          oname => 'FILM_OBJ'
 47        , film_id => :NEW.film_id
 48        , film_name => :NEW.film_name
 49        , release_date => :NEW.release_date
 50        , mpaa_id => :NEW.mpaa_id
 51        , created_by => :NEW.created_by
 52        , creation_date => :NEW.creation_date
 53        , last_updated_by => :NEW.last_updated_by
 54        , last_update_date => :NEW.last_update_date ));
 55
 56    /* Throw exception. */
 57    IF UPDATING THEN
 58      RAISE_APPLICATION_ERROR(-20001,'Film names not updateable.');
 59    END IF;
 60
 61  EXCEPTION
 62    /* Capture an exception. */
 63    WHEN e THEN
 64      ROLLBACK;
 65      dbms_output.put_line('[Trigger Event: '||lv_trigger_event||']');
 66      dbms_output.put_line(SQLERRM);
 67    WHEN OTHERS THEN
 68      dbms_output.put_line(SQLERRM);
 69  END;
 70  /

The film_t1 trigger does much the same thing as the employee_t1 trigger. The difference occurs in the INSERT statement. The film_t1 trigger constructs an old and new film_obj instances to the autonomous procedure.

You use INSERT and UPDATE statements as test cases for the complete model. The INSERT statement would look like the following:

SQL> INSERT INTO employee
  2  ( employee_id
  3  , employee_number
  4  , first_name
  5  , last_name
  6  , created_by
  7  , creation_date
  8  , last_updated_by
  9  , last_update_date )
 10  VALUES
 11  ( employee_seq.NEXTVAL
 12  ,'B98765-678'
 13  ,'Catherine'
 14  ,'Zeta Jones'
 15  , 1
 16  , TRUNC(SYSDATE)
 17  , 1
 18  , TRUNC(SYSDATE));

The INSERT statements should complete without error, but the UPDATE statement should raise an error. You can use the following UPDATE statement:

SQL> UPDATE employee
  2  SET employee_number = 'B98765-678'
  3  ,   first_name = 'Catherine'
  4  ,   last_name = 'Zeta Jones'
  5  ,   created_by = 1
  6  ,   creation_date = TRUNC(SYSDATE)
  7  ,   last_updated_by = 1
  8  ,   last_update_date = TRUNC(SYSDATE)
  9  WHERE first_name = 'Catherine'
 10  AND   middle_name IS NULL
 11  AND   last_name = 'Zeta-Jones';

It throws the following exception:

UPDATE employee
       *
ERROR at line 1:
ORA-04092: cannot ROLLBACK in a trigger
ORA-06512: at "STUDENT.EMPLOYEE_T1", line 64
ORA-20001: No two-part last names without a hyphen.
ORA-04088: error during execution of trigger 'STUDENT.EMPLOYEE_T1'

An INSERT statement lets you test the film table, and UPDATE statement lets you test the film table. The film_t allows the INSERT statement but raises an exception with an UPDATE statement.

Query Data with Standalone PL/SQL

You have the ability to query the results with PL/SQL. The following anonymous block lets you print the results of the trigger architecture for employee_obj records.

SQL> DECLARE
  2    /* Declare a cursor with subcursors. */
  3    CURSOR c IS
  4      SELECT   trigger_log_id
  5      ,        table_name
  6      ,        trigger_event
  7      ,        transaction_status
  8      ,        TREAT(new_instance AS employee_obj)
  9      ,        TREAT(old_instance AS employee_obj)
 10      FROM     trigger_log
 11      WHERE    table_name = 'EMPLOYEE';
 12
 13    /* Declare scalar variables. */
 14    lv_trigger_log_id      NUMBER;
 15    lv_table_name          VARCHAR2(30);
 16    lv_trigger_event       VARCHAR2(6);
 17    lv_transaction_status  VARCHAR2(9);
 18
 19    /* Declare UDT variables. */
 20    lv_old_record  EMPLOYEE_OBJ;
 21    lv_new_record  EMPLOYEE_OBJ;
 22
 23  BEGIN
 24    /* Open base cursor and fetch records until none are found. */
 25    OPEN c;
 26    LOOP
 27      FETCH c
 28      INTO lv_trigger_log_id
 29      ,    lv_table_name
 30      ,    lv_trigger_event
 31      ,    lv_transaction_status
 32      ,    lv_old_record
 33      ,    lv_new_record;
 34      EXIT WHEN c%NOTFOUND;
 35
 36      dbms_output.put_line('========================================');
 37      dbms_output.put_line('Trigger_Log_ID  [Row] : '
 38      || lv_trigger_log_id);
 39      dbms_output.put_line('Table_Name      [Row] : '
 40      || lv_table_name);
 41      dbms_output.put_line('Table_Name      [Row] : '
 42      || lv_trigger_event);
 43      dbms_output.put_line('Transaction     [Row] : '
 44      || lv_transaction_status);
 45      dbms_output.put_line(
 46        '----------------------------------------');
 47      dbms_output.put_line('OName           [Old] : '
 48      || lv_old_record.oname);
 49      dbms_output.put_line('Employee_ID     [Old] : '
 50      || lv_old_record.employee_id);
 51      dbms_output.put_line('Employee_Number [Old] : '
 52      || lv_old_record.employee_number);
 53      dbms_output.put_line('First_Name      [Old] : '
 54      || lv_old_record.first_name);
 55      dbms_output.put_line('Middle_Name     [Old] : '
 56      || lv_old_record.middle_name);
 57      dbms_output.put_line('Last_Name       [Old] : '
 58      || lv_old_record.last_name);
 59      dbms_output.put_line(
 60        '----------------------------------------');
 61      dbms_output.put_line('OName           [New] : '
 62      || lv_new_record.oname);
 63      dbms_output.put_line('Employee_ID     [New] : '
 64      || lv_new_record.employee_id);
 65      dbms_output.put_line('Employee_Number [New] : '
 66      || lv_new_record.employee_number);
 67      dbms_output.put_line('First_Name      [New] : '
 68      || lv_new_record.first_name);
 69      dbms_output.put_line('Middle_Name     [New] : '
 70      || lv_new_record.middle_name);
 71      dbms_output.put_line('Last_Name       [New] : '
 72      || lv_new_record.last_name);
 73    END LOOP;
 74    CLOSE c;
 75
 76    /* Print the close the set. */
 77    dbms_output.put_line(
 78      '========================================');
 79  END;
 80  /

The cursor on lines 4 through 11 includes a key trick for reading the object types on lines 8 and 9. The TREAT function instructs the query to instantiate the base_t column as an employee_obj subtype.

You access the object instance on lines 47 through 58 by referring to the lv_new_record variable. You access the individual field element with a dot notation. The same approach lets you access the lv_old_record variable’s contents.

It generates the following output from the employee table:

========================================
Trigger_Log_ID  [ROW] : 1
Table_Name      [ROW] : EMPLOYEE
Table_Name      [ROW] : INSERT
TRANSACTION     [ROW] : PROCESSED
----------------------------------------
OName           [Old] : EMPLOYEE_OBJ
Employee_ID     [Old] :
Employee_Number [Old] :
First_Name      [Old] :
Middle_Name     [Old] :
Last_Name       [Old] :
----------------------------------------
OName           [NEW] : EMPLOYEE_OBJ
Employee_ID     [NEW] : 1
Employee_Number [NEW] : B98765-678
First_Name      [NEW] : Catherine
Middle_Name     [NEW] :
Last_Name       [NEW] : Zeta-Jones
========================================
Trigger_Log_ID  [ROW] : 2
Table_Name      [ROW] : EMPLOYEE
Table_Name      [ROW] : UPDATE
TRANSACTION     [ROW] : REJECTED
----------------------------------------
OName           [Old] : EMPLOYEE_OBJ
Employee_ID     [Old] : 1
Employee_Number [Old] : B98765-678
First_Name      [Old] : Catherine
Middle_Name     [Old] :
Last_Name       [Old] : Zeta-Jones
----------------------------------------
OName           [NEW] : EMPLOYEE_OBJ
Employee_ID     [NEW] : 1
Employee_Number [NEW] : B98765-678
First_Name      [NEW] : Catherine
Middle_Name     [NEW] :
Last_Name       [NEW] : Zeta Jones
========================================

This article has shown you how to create a framework for the writing trigger results from multiple tables into a single logging table. It’s also shown you how to leverage column substitutability with the base_t type column.

While this example has shown you to query with an anonymous block, you should really use an object table function. You would develop one object table function for each different type of output.

Written by maclochlainn

November 25th, 2018 at 6:26 pm