| recursive triggers. [message #620926] |
Thu, 07 August 2014 05:58  |
 |
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi ,
could you please explain below statement?
Do not create recursive triggers. For example, creating an AFTER UPDATE statement trigger on the Emp_tab table that itself issues an UPDATE statement on Emp_tab, causes the trigger to fire recursively until it has run out of memory.
|
|
|
|
| Re: recursive triggers. [message #620929 is a reply to message #620926] |
Thu, 07 August 2014 06:17  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I cannot explain that statement, because it is nonsense. If you create a trigger that does that, you will find out why.
(Hint: ORA-04091)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: recursive triggers. [message #620939 is a reply to message #620936] |
Thu, 07 August 2014 07:09 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
It's straight forward - doing so creates an infinite loop - or it would be an infinite loop if oracle didn't throw an error at some point and stop it.
You have an after update trigger on table a and the trigger updates table a - you get this:
user issues update statement
table is updated
trigger fires
trigger issues update statement
table is updated
trigger fires
trigger issues update statement
table is updated
trigger fires
trigger issues update statement
table is updated
trigger fires
trigger issues update statement
table is updated
trigger fires
trigger issues update statement
table is updated
trigger fires
trigger issues update statement
table is updated
trigger fires
trigger issues update statement
....
....
oracle throws an error and crashes the process.
@John - mutating table only applies for row level triggers. It's perfectly possible to code a mess like this using statement level triggers. Or row level insert triggers if you're using insert values.
[Updated on: Thu, 07 August 2014 07:10] Report message to a moderator |
|
|
|
|
|
| Re: recursive triggers. [message #620944 is a reply to message #620936] |
Thu, 07 August 2014 07:18 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Assume, for example, you create before or after update trigger on table tbl and trigger body contains UPDATE TBL statement. That what's called recursive trigger. Why? User issues UPDATE TBL statement. Oracle knows there is UPDATE trigger on TBL so it fires the trigger. Trigger issues UPDATE TBL. Again, Oracle knows there is UPDATE trigger on TBL so it fires the trigger and so on. So now we have:
USER ISSUED UPDATE --> TRIGGER --> TRIGGER ISSUED UPDATE --> TRIGGER --> TRIGGER ISSUED UPDATE --> TRIGGER ...
As you can see we got an infinite recursion. Oracle has a safeguard to avoid that infinite recursion. Recursion level is limited to 50. Trying to go deeper raises an error. Now, when we know what recursive triggers are we understand that as in any recursion we must have exit condition otherwise we end up with infinite recursion. So statement "do not create recursive triggers" is not correct. It should say "do not create infinite recursion recursive triggers". For example, recursive triggers are used to avoid "table is mutating" issues.
SY.
|
|
|
|
|
|
| Re: recursive triggers. [message #620948 is a reply to message #620945] |
Thu, 07 August 2014 07:26 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You can get this problem updating a table that contains a single row if you don't pay attention to what you're doing. The number of rows is fairly irrelevant. Especially since it's normally statement level triggers that are involved.
If that doesn't solve your doubt then you need to explain to us in detail what you are trying to do.
|
|
|
|
| Re: recursive triggers. [message #620951 is a reply to message #620940] |
Thu, 07 August 2014 07:32 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
John Watson wrote on Thu, 07 August 2014 08:11Oh, OK, thanks. I was thinking only of FOR EACH ROW triggers.
You can create recursive even with row level triggers:
SQL> create table tbl(n number)
2 /
Table created.
SQL> create or replace
2 trigger tbl_bir
3 before insert
4 on tbl
5 for each row
6 begin
7 insert
8 into tbl
9 values(:new.n + 1);
10 end;
11 /
Trigger created.
SQL> insert
2 into tbl
3 values(1)
4 /
into tbl
*
ERROR at line 2:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "SCOTT.TBL_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.TBL_BIR'
ORA-06512: at "SCOTT.TBL_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.TBL_BIR'
ORA-06512: at "SCOTT.TBL_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.TBL_BIR'
ORA-06512: at "SCOTT.TBL_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.TBL_BIR'
ORA-06512: at "SCOTT.TBL_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.TBL_BIR'
ORA-06512: at "SCOTT.TBL_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.TBL_BIR'
ORA-06512: at "SCOTT.TBL_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.TBL_BIR'
ORA-06512: at "SCOTT.TBL_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.TBL_BIR'
ORA-06512: at "SCOTT.TBL_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.TBL_BIR'
ORA-06512: at "SCOTT.TBL_BIR", line 2
ORA-04088: error during execut
SQL>
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
