Logging Triggers
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:
- Five tables and sequences, and one UDT base type and two subtypes that map to the specific tables
- A reusable autonomous stored procedure
- Two data manipulation language (DML) triggers
- 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.
Subscribe via RSS