Critical Triggers
Oracle Critical and Non-critical Triggers
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 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> 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.
Subscribe via RSS