Thanks for the question, MA.

Asked: March 01, 2016 - 7:30 am UTC

Last updated: November 01, 2019 - 9:06 am UTC

Version: 12c

Viewed 50K+ times! This question is

You Asked

HI TOM SIR
I AM IN BEGNING STAGE OF ORACLE
PLZ EXPLAIN COMPOUND TRIGGERS IN ORACLE WITH SIMPLE EXAMPLES USING EMP TABLE

THANKS&REGARDS
---------------

and Chris said...

Please: DON'T SHOUT!

A compound trigger combines the following four triggers into one:

- before statement
- before row
- after row
- after statement

So if you have a table with two or more of these you could use a compound trigger to rationalize these.

The most common reasons for wanting to use compound triggers are:

- To avoid the mutating table problem
- To collect the affected rows for processing in batch (e.g. to log them).

Here's a simple example for the second use case: collecting inserted rows and logging them:

create table employees as 
  select * from hr.employees;
  
create table emp_log (
  log_timestamp timestamp not null,
  change_type   varchar2(1) not null, 
  employee_id   integer not null
);

create or replace trigger emp_ct
for insert on employees compound trigger 

  type emp_t is table of employees.employee_id%type 
    index by binary_integer;
  emps emp_t;
  
  before statement is 
  begin
    null;
  end before statement;
  
  before each row is 
  begin
    null;
  end before each row;
  
  after each row is 
  begin
    emps(emps.count + 1) := :new.employee_id;
  end after each row;
  
  after statement is 
  begin
  
    forall e in 1 .. emps.count
      insert into emp_log (log_timestamp, change_type, employee_id) 
      values (systimestamp, 'I', emps(e));
      
  end after statement;
  
end;
/

insert into employees (
  employee_id, first_name, last_name, email, hire_date, job_id
) values (
  0, 'Chris', 'Saxon', 'chris.saxon@abc.com', sysdate, 1
) ;

select * from emp_log;

LOG_TIMESTAMP                  C                             EMPLOYEE_ID
------------------------------ - ---------------------------------------
01-MAR-2016 10.07.44.327671000 I                                       0

insert into employees (
  employee_id, first_name, last_name, email, hire_date, job_id
) 
  select 1000+rownum, 'Emp'||rownum, 'Emp'||rownum, 'emp'||rownum||'@abc.com', sysdate, 1
  from   dual 
  connect by level <= 100;

select * from emp_log
where  rownum <= 10;

LOG_TIMESTAMP                  C                             EMPLOYEE_ID
------------------------------ - ---------------------------------------
01-MAR-2016 10.07.44.327671000 I                                       0
01-MAR-2016 10.07.45.232205000 I                                    1001
01-MAR-2016 10.07.45.232205000 I                                    1002
01-MAR-2016 10.07.45.232205000 I                                    1003
01-MAR-2016 10.07.45.232205000 I                                    1004
01-MAR-2016 10.07.45.232205000 I                                    1005
01-MAR-2016 10.07.45.232205000 I                                    1006
01-MAR-2016 10.07.45.232205000 I                                    1007
01-MAR-2016 10.07.45.232205000 I                                    1008
01-MAR-2016 10.07.45.232205000 I                                    1009

For further examples, check out:

http://viralpatel.net/blogs/compound-triggers-in-oracle-11g-tutorial-example/
https://oracle-base.com/articles/11g/trigger-enhancements-11gr1#compound_triggers

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

excelent

MA NAIDU, March 01, 2016 - 10:57 am UTC

thanks

please give an example to avoid table mutating error

A reader, June 07, 2016 - 5:51 pm UTC

Hi Tom, please give an example to avoid table mutating error

June 08, 2016 - 1:04 am UTC

Simple example here

https://oracle-base.com/articles/11g/trigger-enhancements-11gr1#compound_triggers

What about views?

Mario, May 03, 2017 - 1:51 pm UTC

I know we can create a compound trigger for view like this:

create or replace trigger [NAME] for insert or delete or update on [VIEW]
compound trigger

instead of each row is 
begin
    null;
end instead of each row;

end;

But. What is the purpose of a compound trigger with only one section. I tried to research others sections like after, before or just "instead of" (without each row). But i didn´t found.

It´s possible to do a "after statement", not for each row, in a trigger view?

May 04, 2017 - 2:19 am UTC

If my memory is correct (and it might not be :-)), in the early versions of 11g when these first came out, I think FOLLOWS and PRECEDES clauses only work for "same type" triggers, ie, normal to normal, or compound to compound, which might be a justification for writing a trigger in this way.

Use of after row block in given example

Harika, October 03, 2017 - 6:31 am UTC

Thanks a lot for giving such an example.

Can you please explain why you are assigning emp id to the array in after-eachrow block, as we are acheiving the same(logging records in table) by after-statement block.

October 06, 2017 - 1:20 am UTC

You need to collect the ids to log to insert them into the logging table!

If you don't do this for each row, the collection is empty so you log nothing.

Compound trigger behaviour

Rajesh, October 30, 2019 - 7:20 am UTC

Database detail:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

TABLE SCRIPT:

 create table "employees 
   (employee_id number(6,0), 
 first_name varchar2(20 byte), 
 last_name varchar2(25 byte) not null enable, 
 email varchar2(25 byte) not null enable, 
 phone_number varchar2(20 byte), 
 hire_date date not null enable, 
 job_id varchar2(10 byte) not null enable, 
 salary number(8,2), 
 commission_pct number(2,2), 
 manager_id number(6,0), 
 department_id number(4,0)
   );

TRIGGER CODE:
create or replace TRIGGER emp_ct FOR
INSERT ON employees
COMPOUND TRIGGER
TYPE emp_t IS TABLE OF employees%rowtype INDEX BY BINARY_INTEGER;
emps emp_t;
ind SIMPLE_INTEGER := 0;
BEFORE STATEMENT IS BEGIN
NULL;
END BEFORE STATEMENT;
BEFORE EACH ROW IS BEGIN
NULL;
END BEFORE EACH ROW;
AFTER EACH ROW IS BEGIN
ind := ind + 1;
emps(ind).employee_id :=:NEW.employee_id;
emps(ind).first_name :=:NEW.first_name;
DBMS_OUTPUT.PUT_LINE('AFTER EACH ROW emps.COUNT='|| emps.COUNT);
END AFTER EACH ROW;
AFTER STATEMENT IS BEGIN
DBMS_OUTPUT.PUT_LINE('AFTER STATEMENT emps.COUNT='|| emps.COUNT);
END AFTER STATEMENT;
END;
Test CASE-1:
When I insert data into EMPLOYEES using construct insert into EMPLOYEES () values () as given below why the trigger statement trigger fire for each row. I am not able to achieve bulk insert. For testing purpose, I am not actually inserting data into table just outputting.
REM INSERTING into EMPLOYEES
SET DEFINE OFF;

Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (198,'Donald','OConnell','DOCONNEL','650.507.9833',to_date('21-JUN-07','DD-MON-RR'),'SH_CLERK',2600,null,124,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (199,'Douglas','Grant','DGRANT','650.507.9844',to_date('13-JAN-08','DD-MON-RR'),'SH_CLERK',2600,null,124,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (200,'Jennifer','Whalen','JWHALEN','515.123.4444',to_date('17-SEP-03','DD-MON-RR'),'AD_ASST',4400,null,101,10);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (201,'Michael','Hartstein','MHARTSTE','515.123.5555',to_date('17-FEB-04','DD-MON-RR'),'MK_MAN',13000,null,100,20);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (202,'Pat','Fay','PFAY','603.123.6666',to_date('17-AUG-05','DD-MON-RR'),'MK_REP',6000,null,201,20);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (203,'Susan','Mavris','SMAVRIS','515.123.7777',to_date('07-JUN-02','DD-MON-RR'),'HR_REP',6500,null,101,40);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (204,'Hermann','Baer','HBAER','515.123.8888',to_date('07-JUN-02','DD-MON-RR'),'PR_REP',10000,null,101,70);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (205,'Shelley','Higgins','SHIGGINS','515.123.8080',to_date('07-JUN-02','DD-MON-RR'),'AC_MGR',12008,null,101,110);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (206,'William','Gietz','WGIETZ','515.123.8181',to_date('07-JUN-02','DD-MON-RR'),'AC_ACCOUNT',8300,null,205,110);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (100,'Steven','King','SKING','515.123.4567',to_date('17-JUN-03','DD-MON-RR'),'AD_PRES',24000,0.4,null,90);

OUTPUT:
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
....
Test CASE-2:
When I perform the insert operation using construct: INSERT INTO ... SELECT
I can see for each insert, rows are accumulated in array and the statement level trigger fires only once.
I am trying to understand this behavior. Could you please explain me.

INSERT INTO employees (
  employee_id, first_name, last_name, email, hire_date, job_id
) 
  SELECT 1000+ROWNUM, 'Emp'||ROWNUM, 'Emp'||ROWNUM, 'emp'||ROWNUM||'@abc.com', sysdate, 1
  FROM   dual 
  CONNECT BY LEVEL <= 10;

OUTPUT

AFTER EACH ROW emps.COUNT=1
AFTER EACH ROW emps.COUNT=2
AFTER EACH ROW emps.COUNT=3
AFTER EACH ROW emps.COUNT=4
AFTER EACH ROW emps.COUNT=5
AFTER EACH ROW emps.COUNT=6
AFTER EACH ROW emps.COUNT=7
AFTER EACH ROW emps.COUNT=8
AFTER EACH ROW emps.COUNT=9
AFTER EACH ROW emps.COUNT=10
AFTER STATEMENT emps.COUNT=10

November 01, 2019 - 9:06 am UTC

In the first example you have TEN STATEMENTS each inserting one row.

In the second there is ONE STATEMENT inserting ten rows.

The trigger fires once per statement.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

excelent

please give an example to avoid table mutating error

What about views?

Use of after row block in given example

Compound trigger behaviour

More to Explore

PL/SQL demos

PL/SQL docs

Compound triggers

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

excelent

please give an example to avoid table mutating error

What about views?

Use of after row block in given example

Compound trigger behaviour

More to Explore

PL/SQL demos

PL/SQL docs