Can you please give a example for use the compound trigger to this situation
Suresh E, November 02, 2016 - 11:05 pm UTC
Hi,
Thanks for your quick response.
I want to log all the records were deleted from TABLE1 in TABLE1_HISTORY table.
I agree with you row level can help us to accomplish my expectation, But I want the trigger should be called only once like Statement level trigger.
Whether I can use the reference clause in the statement level trigger?
I'm getting below error when I used reference clause in the statement level trigger.
ORA-04082: NEW or OLD references not allowed in table level triggers.
If you have examples for the compound trigger with a similar situation. it's really helpful for me.
Regards,
Suresh. E
November 03, 2016 - 8:36 pm UTC
Compound Trigger Example
Rajeshwaran Jeyabal, November 03, 2016 - 7:16 am UTC
http://docs.oracle.com/database/121/LNPLS/triggers.htm#LNPLS2005 demo@ORA12C> create table emp_history as
2 select *
3 from emp
4 where 1 = 0;
Table created.
demo@ORA12C> create or replace trigger emp_hist_track
2 for delete on emp
3 compound trigger
4 type array is table of emp%rowtype;
5 l_array array := array();
6
7 after each row is
8 begin
9 l_array.extend;
10 l_array(l_array.count).empno := :old.empno;
11 l_array(l_array.count).ename := :old.ename;
12 l_array(l_array.count).job := :old.job;
13 l_array(l_array.count).mgr := :old.mgr;
14 l_array(l_array.count).hiredate := :old.hiredate;
15 l_array(l_array.count).sal := :old.sal;
16 l_array(l_array.count).comm := :old.comm;
17 l_array(l_array.count).deptno := :old.deptno;
18 end after each row;
19
20 after statement is
21 begin
22 forall i in 1..l_array.count
23 insert into emp_history values l_array(i);
24 l_array.delete;
25 end after statement;
26 end;
27 /
Trigger created.
demo@ORA12C> select * from emp where deptno = 10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-1981 2450 10
7839 KING PRESIDENT 17-NOV-1981 5000 10
7934 MILLER CLERK 7782 23-JAN-1982 1300 10
3 rows selected.
demo@ORA12C> select * from emp_history;
no rows selected
demo@ORA12C> delete from emp where deptno = 10 ;
3 rows deleted.
demo@ORA12C> select * from emp_history;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-1981 2450 10
7839 KING PRESIDENT 17-NOV-1981 5000 10
7934 MILLER CLERK 7782 23-JAN-1982 1300 10
3 rows selected.
demo@ORA12C>
Thanks!!
Suresh E, November 03, 2016 - 4:28 pm UTC
Hi,
Thanks for your quick reply and example.
The Compound Trigger(the above) looks similar Row level trigger. The compound trigger will be called recursively for each row right?
or It will be called only once?
Regards,
Suresh. E
November 03, 2016 - 8:38 pm UTC
The trigger itself is only runs once. But the before/after row sections run once for each row processed.
Thanks a lot!!!
Suresh E, November 03, 2016 - 9:26 pm UTC
Thanks once again