Home » SQL & PL/SQL » SQL & PL/SQL » about triggers
about triggers [message #201420] Sat, 04 November 2006 02:46 Go to next message
ajomara
Messages: 3
Registered: November 2006
Junior Member
hai,can any one tell me what is the difference between a row level trigger and the statement level trigger with examples.
and also about row level trigger how it could works in below
create or replace trigger a
before update on emp
for each row
begin
update emp set sal=sal+100 where empno=7788;
end;
/
here my question is before updating how the trigger fires




thank you
Re: about triggers [message #201423 is a reply to message #201420] Sat, 04 November 2006 02:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And of course you read all 354,000 hits found when you googled for row level trigger, right?
And after you did, you read the complete documentation?
Re: about triggers [message #201425 is a reply to message #201420] Sat, 04 November 2006 02:57 Go to previous messageGo to next message
jiltin
Messages: 44
Registered: September 2002
Member
1. Row level trigger and the statement level trigger difference

Assume, you have 10 employees in "emp" table.
Assume, You have a statement trigger that captures login information in "trig_table" whoever updates this table.

Update emp set salary = salary+15;

Since you have statement trigger, after executing the update, you will find one record in "trig_table"

Instead of statement trigger, if you have row level trigger, you will see 10 records in "trig_table" that fires each row update.

2. My question is before updating how the trigger fires

The example you have will not work. This failure is called "mutating trigger" as it triggers endless loop!

Hope answers your question.




Re: about triggers [message #201431 is a reply to message #201423] Sat, 04 November 2006 03:11 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Are you check below trigger.
create or replace trigger a 
before update on emp 
for each row
begin
update emp set sal=sal+100 where empno=7788;
end;
/
if not then check it.

ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not 
see it
ORA-06512: at "SCOTT.A", line 2
ORA-04088: error during execution of trigger 'SCOTT.A'



Re: about triggers [message #201445 is a reply to message #201425] Sat, 04 November 2006 05:49 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
This failure is called "mutating trigger" as it triggers endless loop!

The mutating table error has not necessarily to do with endless loops. It has to do with the unknown state the table is in at the time of the trigger firing. Even if you would only select from the table you would get the same error.

But, as I pointed out before: it is all in the docs.
Previous Topic: Timebound trigger
Next Topic: function - two nested cursor for loop ???
Goto Forum:
  


Current Time: Thu Dec 08 23:46:28 CST 2016

Total time taken to generate the page: 0.08044 seconds