Oracle Trigger Basics
Oracle Trigger Basics
Once you master the basics of inserting, updating, and deleting data from the database, you typically learn about database triggers. Database triggers are coding elements that let you manage events beyond the limit of database constraints.
Before you can appreciate the power of database triggers, you need to understand what database constraints can and can’t do. Then, together we’ll explore how you can implement database triggers.
Database constraints let you manage events. A primary key constraint guarantees a column or a set of columns are unique and not null. A foreign key constraint guarantees a column only contains a value or set of values found in the primary key. A not null constraint makes a column mandatory when you insert or update a row in a table. A unique constraint guarantees a column or set of columns only exist in one row of a table. A check constraint guarantees a column’s value must comply with a set of rules defined with a row of data.
Database constraints do have limits. For example, a foreign key constraint doesn’t guarantee the right foreign key value because it only guarantees a foreign key value is a valid value in a list of possible values. That means it’s possible to insert or update a foreign key column or set of columns with an incorrect foreign key value. Only a database trigger can guarantee the insert or update of a correct foreign key value. The database trigger verifies the correct behavior by validating conditions before an insert or update.
While a unique constraint guarantees uniqueness and a check constraint guarantees compliance against a set of rules in a row, only a database trigger can guarantee the maximum number of like rows in a table that comply with a rule. Also, there is no constraint that manages inserts, updates, and deletes with dependencies on data in other tables.
A Data Manipulation Language (DML) trigger lets you manage these shortfalls and more. You have two options when implementing DML triggers. One implements a statement trigger and the other implements a row-level trigger. A statement-level trigger runs once for any and all rows affected by an INSERT, UPDATE, or DELETE statement. A row-level trigger runs once for each row affected by an INSERT, UPDATE, or DELETE statement.
Both of these triggers have two components – a trigger and a trigger body. The trigger defines what event to manage and the trigger body implements the logic that manages the event.
Statement-Level Triggers
You should create two tables to work with statement-level triggers. The first is the avenger table and the second is the avenger_log table. Your inserts, updates, and deletes to the avenger table act as events that fire triggers. Statement-level triggers can be defined to run before or after INSERT, UPDATE, and DELETE statements. Statement-level triggers are narrowly scoped events and they log message data to the avenger_log table.
This is the definition of the avenger table:
SQL> CREATE TABLE avenger 2 ( avenger_id NUMBER 3 , avenger_name VARCHAR2(30) 4 , first_name VARCHAR2(20) 5 , last_name VARCHAR2(20)); |
This is the definition of the avenger_log table:
SQL> CREATE TABLE avenger_log 2 ( avenger_log_id NUMBER 3 , trigger_name VARCHAR2(30) 4 , trigger_timing VARCHAR2(6) 5 , trigger_event VARCHAR2(6) 6 , trigger_type VARCHAR2(12)); |
The following avenger_t1 creates a BEFORE INSERT statement trigger:
SQL> CREATE OR REPLACE TRIGGER avenger_t1 2 BEFORE INSERT ON avenger 3 DECLARE 4 /* Declare local trigger-scope variables. */ 5 lv_sequence_id NUMBER := avenger_log_s.NEXTVAL; 6 lv_trigger_name VARCHAR2(30) := 'AVENGER_T1'; 7 lv_trigger_event VARCHAR2(6) := 'INSERT'; 8 lv_trigger_type VARCHAR2(12) := 'STATEMENT'; 9 lv_trigger_timing VARCHAR2(6) := 'BEFORE'; 10 BEGIN 11 /* Log event into the avenger_log table. */ 12 INSERT INTO avenger_log 13 ( avenger_log_id 14 , trigger_name 15 , trigger_event 16 , trigger_type 17 , trigger_timing ) 18 VALUES 19 ( lv_sequence_id 20 , lv_trigger_name 21 , lv_trigger_event 22 , lv_trigger_type 23 , lv_trigger_timing ); 24 END avenger_t1; 25 / |
Lines 1 and 2 declare the trigger. Lines 3 through 24 implements an anonymous PL/SQL block as the trigger’s body, and line 25 executes the trigger. Lines 6 through 9 store literal values for the trigger’s name, event, type, and timing. The trigger uses these literal values when logging events to the avenger_log table.
You access the data catalog information about triggers in the DBA_, ALL_, and USER_TRIGGERS views in a non-containerized database. Triggers also exist in those views for containerized databases (CDB). CDBs have an additional CDB_TRIGGERS view that stores triggers. The trigger body is stored in the TRIGGER_BODY column of those views in a LONG column.
You can create an AFTER STATEMENT trigger by simply changing the first two lines or the trigger declaration, as follows:
SQL> CREATE OR REPLACE TRIGGER avenger_t1 2 AFTER INSERT ON avenger |
Then, you need to change values of the string literals on lines 6, 7, and 9 as follows:
6 lv_trigger_name VARCHAR2(30) := 'AVENGER_T2'; 7 lv_trigger_event VARCHAR2(6) := 'INSERT'; 8 lv_trigger_type VARCHAR2(12) := 'STATEMENT'; 9 lv_trigger_timing VARCHAR2(6) := 'AFTER'; |
Compiling the database triggers, let’s insert a row into the avenger table, like this:
SQL> INSERT INTO avenger 2 VALUES 3 ( avenger_s.NEXTVAL 4 ,'Captain America' 5 ,'Steven' 6 ,'Rogers'); |
Then, you can query the avenger_log table, like this:
SQL> COLUMN avenger_log_id FORMAT 999 HEADING "Avenger|Log ID #" SQL> COLUMN trigger_name FORMAT A12 HEADING "Trigger|Name" SQL> COLUMN trigger_timing FORMAT A7 HEADING "Trigger|Timing" SQL> COLUMN trigger_event FORMAT A7 HEADING "Trigger|Event" SQL> COLUMN trigger_type FORMAT A12 HEADING "Trigger|Type" SQL> SELECT * FROM avenger_log; |
It returns two rows – one row from the avenger_t1 trigger and the other from the avenger_t2 trigger:
Avenger TRIGGER TRIGGER TRIGGER TRIGGER LOG ID # Name TIMING Event TYPE -------- ------------ ------- ------- ---------- 1 AVENGER_T2 AFTER INSERT STATEMENT 2 AVENGER_T1 BEFORE INSERT STATEMENT |
Both of the triggers use the avenger_log_s1 sequence. You may notice that the AFTER STATEMENT trigger ran before the BEFORE STATEMENT trigger. That shows you that triggers aren’t sequenced by default, even when you think that the timing event should sequence them.
Oracle lets you sequence triggers by using the FOLLOWS clause when you define database triggers. The following modifies the avenger_t2 by adding a FOLLOWS clause on line 3, and it uses ellipses to shorten the example:
SQL> CREATE OR REPLACE TRIGGER avenger_t2 2 BEFORE INSERT ON avenger 3 FOLLOWS avenger_t1 4 DECLARE … 11 BEGIN … 25 END avenger_t2; 26 / |
The testing script drops and creates the avenger_log table before creating fresh copies of the avenger_t1 and avenger_t2 triggers. The script lets you re-query the avenger_log table without the baggage of the previous two rows.
Like before, it returns two rows – one row from the avenger_t1 trigger and the other from the avenger_t2 trigger:
Avenger TRIGGER TRIGGER TRIGGER TRIGGER LOG ID # Name TIMING Event TYPE -------- ------------ ------- ------- ----------- 1 AVENGER_T1 BEFORE INSERT STATEMENT 2 AVENGER_T2 AFTER INSERT STATEMENT |
You should note that the BEFORE STATEMENT trigger now runs before the AFTER STATEMENT trigger. The FOLLOWS clause lets you guarantee the order of trigger execution.
As you can see, statement-level triggers don’t give us the ability to see, change, or log the before and after values of data. You can do that with row-level triggers.
Row-Level Triggers
Row-level triggers let you see the initial column values you add into a table with an INSERT statement. Row-level triggers let you see existing column values and the column values provided by an UPDATE statement. The DELETE statement only provides the existing column values to a trigger because it removes the row from the database. Inside the row-level trigger you can change new values based on rules that you put in code inside the database trigger.
The avenger_log table requires major changes to support a row-level database trigger because it needs to store the old and new values of a table’s data column. Data columns hold values that describe an instance of the table. A data column or set of data columns should also define a unique key that makes each row unique in a table.
After engineering a table, you should also add a surrogate key column. A surrogate (stand-in) key column contains a value generated from a sequence, and a surrogate key is generally unrelated to the subject of a table. You use the natural key to find a unique row in a table, and you copy the surrogate key value when you want a foreign key to link another row with the row identified by the surrogate key.
Both the surrogate key column and natural key (one or more columns) should both identify unique rows. That means for every surrogate key there should be a natural key.
The data columns in the avenger table are the avenger_name, first_name, and last_name columns. You should define an old and new column for each of the data columns when you create a logging table.
This defines the new avenger_log table:
SQL> CREATE TABLE avenger_log 2 ( avenger_log_id NUMBER 3 , trigger_name VARCHAR2(30) 4 , trigger_timing VARCHAR2(6) 5 , trigger_event VARCHAR2(6) 6 , trigger_type VARCHAR2(12) 7 , old_avenger_name VARCHAR2(20) 8 , old_first_name VARCHAR2(20) 9 , old_last_name VARCHAR2(20) 10 , new_avenger_name VARCHAR2(20) 11 , new_first_name VARCHAR2(20) 12 , new_last_name VARCHAR2(20)); |
The first row-level database trigger you create runs when an INSERT statement adds a new row to the avenger table. The code exists below:
SQL> CREATE OR REPLACE TRIGGER avenger_t3 2 BEFORE INSERT ON avenger 3 FOR EACH ROW 4 DECLARE 5 /* Declare local trigger-scope variables. */ 6 lv_sequence_id NUMBER := avenger_log_s.NEXTVAL; 7 lv_trigger_name VARCHAR2(30) := 'AVENGER_T3'; 8 lv_trigger_event VARCHAR2(6) := 'INSERT'; 9 lv_trigger_type VARCHAR2(12) := 'FOR EACH ROW'; 10 lv_trigger_timing VARCHAR2(6) := 'BEFORE'; 11 BEGIN 12 /* Log event into the avenger_log table. */ 13 INSERT INTO avenger_log 14 ( avenger_log_id 15 , trigger_name 16 , trigger_event 17 , trigger_type 18 , trigger_timing 19 , old_avenger_name 20 , old_first_name 21 , old_last_name 22 , new_avenger_name 23 , new_first_name 24 , new_last_name ) 25 VALUES 26 ( lv_sequence_id 27 , lv_trigger_name 28 , lv_trigger_event 29 , lv_trigger_type 30 , lv_trigger_timing 31 , :old.avenger_name 32 , :old.first_name 33 , :old.last_name 34 , :NEW.avenger_name 35 , :NEW.first_name 36 , :NEW.last_name ); 37 END avenger_t3; 38 / |
Line 3 declares the avenger_t3 trigger as a row-level trigger. Lines 31 through 36 inserts the old and new values from the row of the avenger table when the INSERT statement runs with the following three values:
SQL> INSERT INTO avenger 2 VALUES 3 ( avenger_s.NEXTVAL 4 ,'Capt. America' 5 ,'Steven' 6 ,'Rogers'); |
Since the script drops and recreates the avenger and avenger_log tables and drops the avenger_t1 and avenger_t2 statement-level triggers, you can write a query to return only the test row. The following anonymous PL/SQL block let’s you print the old and new column values next to one another. The program helps make the row-level trigger’s ability to see before and after values clear.
SQL> SET SERVEROUTPUT ON SIZE UNLIMITED SQL> BEGIN 2 FOR i IN (SELECT * FROM avenger_log) LOOP 3 dbms_output.put_line( 4 'Trigger Name [' 5 || i.trigger_name||']'); 6 dbms_output.put_line( 7 'Trigger Event [' 8 || i.trigger_event||']'); 9 dbms_output.put_line( 10 'Trigger Type [' 11 || i.trigger_type||']'); 12 dbms_output.put_line( 13 'Trigger Timing [' 14 || i.trigger_timing||']'); 15 dbms_output.put_line( 16 'Avenger Name [' 17 || i.old_avenger_name||'][' 18 || i.new_avenger_name||']'); 19 dbms_output.put_line( 20 'First Name [' 21 || i.old_first_name||'][' 22 || i.new_first_name||']'); 23 dbms_output.put_line( 24 'Last Name [' 25 || i.old_last_name||'][' 26 || i.new_last_name||']'); 27 END LOOP; 28 END; 29 / |
This anonymous block prints the following from the avenger_log table:
TRIGGER Name [AVENGER_T3] TRIGGER Event [INSERT] TRIGGER TYPE [FOR EACH ROW] TRIGGER TIMING [BEFORE] Avenger Name [][Capt. America] FIRST Name [][Steven] LAST Name [][Rogers] |
This has demonstrated how you write a row-level trigger against an INSERT event. You should note that the old values for the avenger_name, first_name, and last_name are null values between the square brackets. Next, you should examine how to write a row-level trigger against more than one type of event.
The Oracle Database lets you write individual triggers for INSERT, UPDATE, or DELETE statement, or a single trigger to manage INSERT, UPDATE, and DELETE events. The following modifies the avenger_t3 trigger so that it works for an INSERT, UPDATE, and DELETE events:
SQL> CREATE OR REPLACE TRIGGER avenger_t3 2 BEFORE INSERT OR UPDATE OR DELETE ON avenger 3 FOR EACH ROW |
Line 2 of the previous trigger is where we change the avenger_t3 trigger to also work with UPDATE and DELETE events. Then, we need to change one other line and then add a small IF-block to the trigger.
Line 8 of the original trigger assigns a default value to the lv_trigger_event variable, but you need to remove the value assignment. The modified line looks like this:
8 lv_trigger_event VARCHAR2(6); |
You also need to add an IF-block that manages Data Manipulation Language (DML) event functions. The IF-block should be the first thing in the execution block of the trigger body, and it should implement this logic:
11 BEGIN 12 /* Evaluate and assign event for logging. */ 13 IF INSERTING THEN lv_trigger_event := 'INSERT'; 14 ELSIF UPDATING THEN lv_trigger_event := 'UPDATE'; 15 ELSIF DELETING THEN lv_trigger_event := 'DELETE'; 16 END IF; ... |
The INSERTING event function on line 13 occurs when an INSERT statement activates the trigger. The UPDATING and DELETING event functions on lines 14 and 15 occur when a respective UPDATE or DELETE statement activity fires a trigger.
The following UPDATE statement now creates an event that the avenger_t3 trigger is monitoring:
SQL> UPDATE avenger 2 SET avenger_name = 'Captain America' 3 WHERE avenger_name = 'Capt. America'; |
Next, let’s test a DELETE statement with the following:
SQL> DELETE 2 FROM avenger 3 WHERE avenger_name = 'Captain America'; |
The following anonymous block program lets you see the log values inserted into the avenger_log table from the INSERT, UPDATE, and DELETE statement triggers:
SQL> SET SERVEROUTPUT ON SIZE UNLIMITED SQL> BEGIN 2 FOR i IN (SELECT * FROM avenger_log) LOOP 3 dbms_output.put_line( 4 'Row Number [' 5 || i.avenger_log_id ||'][' 6 || i.trigger_event ||']'); 7 dbms_output.put_line( 8 'Avenger Name [' 9 || i.old_avenger_name ||'][' 10 || i.new_avenger_name ||']'); 11 dbms_output.put_line( 12 'First Name [' 13 || i.old_first_name ||'][' 14 || i.new_first_name ||']'); 15 dbms_output.put_line( 16 'Last Name [' 17 || i.old_last_name ||'][' 18 || i.new_last_name ||']'); 19 END LOOP; 20 END; 21 / |
The anonymous block returns the following:
Row Number [1][INSERT] Avenger Name [][Capt. America] First Name [][Steven] Last Name [][Rogers] Row Number [2][UPDATE] Avenger Name [Capt. America][Captain America] First Name [Steven][Steven] Last Name [Rogers][Rogers] Row Number [3][DELETE] Avenger Name [Captain America][] First Name [Steven][] Last Name [Rogers][] |
You should notice the old values for the INSERT event are missing because there isn’t a row before running the INSERT statement. Likewise, you should notice the new values for the DELETE event are missing because there isn’t a row after running a DELETE statement. Only the UPDATE event has both an old and new value because the row exists before and after any change. The old values hold the row’s values before the UPDATE statement and the new values hold the row’s values after the UPDATE statement.
Subscribe via RSS