[PL/SQL] Trigger

binh12A3

6 min readSep 23, 2021

😎😎

1. DML trigger

DML trigger has 2 types : row-level and statement_level

What is the order of execution if there is more than one trigger exists ?

Example 1 : Let’s insert data and see the order execution of triggers

Example 2 : Let’s create additional 4 triggers for UPDATE event and see the order execution of triggers

Example 3 : in below example you you see that 4 rows are printed out, since we have 2 rows are updated,

Example 4 : When there is no row updated, only statement level triggers are invoked.

2. INSTEAD OF trigger

DML trigger is triggered on top of tables, and INSTEAD OF trigger is triggered on top of the Views.
As you might know we could insert data into a simple view (i.e : 1 table), but we can’t do it on a complex view (i.e : more than 1 table). Since Oracle doesn’t know which is the underlying table to be inserted and will throw an error.

So in order to insert into a complex view, we’ll use “INSTEAD OF trigger” to construct/redirect the flow to expected sub tables.

3. COMPOUND trigger

Compound Trigger is a combination of 4 individual trigger’s sessions. It’s not mandatory to always have 4 individual trigger’s sessions, it’s up to you.

And the same with normal triggers, you can specific events like : INSERTING, UPDATING, DELETING

One of the most advantage of Compound Trigger : Effectively load the changes to another table using bulk collect.

Example :

Let’s make an demo to store the history of updating “sal” in table “emp” into the history table “t_change_log”

Here we’ll approach by using AFTER UPDATE ROW REVEL trigger. It’s working OK, but the problem with this solution when we want to update all the table, then the trigger will be fired for each rows which cause a really worse performance issue.

We could resolve this performace issue by creating e a pck to hold a collection variable, then create 2 triggers. Here AFTER UPDATE ROW LEVEL TRIGGER will be used only to collect data into the collection variable. Then finally, the AFTER UPDATE STATEMENT LEVEL TRIGGER will perform a bulk insert.

Instead of using pck + 2 triggers as above, we could just simply use Compound Trigger.

Additional notes

1. What is Mutating Trigger ?

The Mutating trigger error happens when within the body of a row level trigger tries to read or modify data from a same table (where the trigger is fired) directly, or indirectly via function call which will goto that table.

Example 1 : we’ll create the trigger to ensure the new updated salary can’t greater than the defined hardcoded maximum salary.

Example 2 : If the job is CEO, then we’ll do the update, else we’ll check to ensure the new updated salary of other jobs can’t greater than the CEO’s salary. So instead of hardcode the maximum salary, we’ll select it from table emp_t. Now if we do a update, we’ll see the mutating error.

Example 2 :

--1. create pck
CREATE OR REPLACE PACKAGE trigger_api AS
PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2);END trigger_api;
/
CREATE OR REPLACE PACKAGE BODY trigger_api AS
PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2) IS
  l_count  NUMBER(10) := 0;
BEGIN
  SELECT COUNT(*)
  INTO   l_count
  FROM   tab1;INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time)
  VALUES (tab1_audit_seq.NEXTVAL, p_action, p_id, l_count, SYSTIMESTAMP);
END tab1_row_change;END trigger_api;
/--2. create trigger
CREATE OR REPLACE TRIGGER tab1_ariu_trg
AFTER INSERT OR UPDATE ON tab1
FOR EACH ROW
BEGIN
  IF inserting THEN
    trigger_api.tab1_row_change(p_id => :new.id, p_action => 'INSERT');
  ELSE
    trigger_api.tab1_row_change(p_id => :new.id, p_action => 'UPDATE');
  END IF;
END;
/--3. test
INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'ONE');
INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'ONE')
                                           *
ERROR at line 1:
ORA-04091: table TEST.TAB1 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRIGGER_API", line 7
ORA-06512: at "TEST.TAB1_ARIU_TRG", line 3
ORA-04088: error during execution of trigger 'TEST.TAB1_ARIU_TRG'SQL>

2. How to solve Mutating Trigger ?

We could solve it by creating a pck to hold a global variable, and moving the SELECT logic of row-level-trigger into statement-level-trigger. It’ll solve the mutating error, because now the row level trigger won’t read directly from table but indirectly via pck variable.