Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Triggers
icon5.gif  Mutating Triggers [message #347776] Sat, 13 September 2008 21:21 Go to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
Hello All,

I have 2 queries regarding Triggers as:-

1>
Can we change the value of :NEW in a trigger?
Like -
CREATE TRIGGER t1
AFTER UPDATE ON emp
BEGIN
:NEW := 'xyz';
END;

2>
If my trigger is mutating, means like below given:
CREATE TRIGGER t1
AFTER UPDATE ON emp
BEGIN
SELECT eno, enamen esal FROM emp
WHERE enam LIKE 'P%';
END;

And, now I try to UPDATE the table emp, I will get the mutating trigger error.
But, now if, I want to use this without causing error, Is there any clause in Oracle?

Thanks and Regards,
-Prachi
Re: Mutating Triggers [message #347778 is a reply to message #347776] Sat, 13 September 2008 22:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above


You choose to not follow Posting Guidelines.
We choose to not answer your question(s).

Re: Mutating Triggers [message #347781 is a reply to message #347778] Sat, 13 September 2008 22:20 Go to previous messageGo to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
I am very sorry if I made any mistakes.

As, I provided all relevant information also.
I am polite also.

But can you please notify exactly what fault I made?

Thanks and Regards,
-Prachi
Re: Mutating Triggers [message #347782 is a reply to message #347776] Sat, 13 September 2008 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Below from Posting Guidelines if you would actually read them!

Practice


Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session), including errors and/or why the result is not what you want.
Do not describe, explain or report - show us!
Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
Provide your expected result set and explain the rules/reasons that lead to it.
If you want to post error codes or SQL*Plus output, just copy everything that is on your screen when the error occurred, for instance:

SQL> insert into t2 values (t2_s.nextval, x1) returning id into :x2;
insert into t2 values (t2_s.nextval, x1) returning id into :x2
*
ERROR at line 1:
ORA-00984: column not allowed here


Which of the above do you do?
ERROR? What error? I don't see any error?

Is GOOGLE & the search function on this forum broken for you?
You are NOT the first person who saw this error.
The solution is here if you'd look for it!
Re: Mutating Triggers [message #347784 is a reply to message #347782] Sat, 13 September 2008 23:33 Go to previous messageGo to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
Ok, Thanks.

About Mutating Error:

I get as:
ORA-04091: table emp is mutating, trigger/function may not see iT
eRROR DURING TRIGGER EXECUTION.

Thanks
-Prachi
Re: Mutating Triggers [message #347785 is a reply to message #347776] Sat, 13 September 2008 23:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above


Is GOOGLE & the search function on this forum broken for you?
You are NOT the first person who saw this error.
The solution is here if you'd look for it!
Re: Mutating Triggers [message #347787 is a reply to message #347776] Sat, 13 September 2008 23:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1>
* You cannot change :NEW in an AFTER trigger as it happens AFTER the update.
* You cannot access :NEW in a statement trigger only in a row trigger

2>
You cannot read or write the table you are currently modifying in a trigger as it is not in a consistent state for a transactional point of view

Read!

Database Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers

Tom Kyte The Trouble with Triggers

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
In the end, always post your Oracle version (4 decimals).


Regards
Michel
Re: Mutating Triggers [message #347789 is a reply to message #347776] Sun, 14 September 2008 00:01 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
1> you cant use :new or :old in table level triggers.
2> and here, you cant select the same table that you are updating.
icon5.gif  Re: Mutating Triggers [message #347804 is a reply to message #347787] Sun, 14 September 2008 02:09 Go to previous messageGo to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
Thanks Sir,

As you said:

* You cannot change :NEW in an AFTER trigger as it happens AFTER the update.

I am sorry , I did not mention in the example the trigger as a ROW Level trigger. It must be a Row Level Trigger for using NEW and OLD.

Does that say, I can actually change th value :NEW in BEFORE Update trigger? Like :NEW := 'abc?

Thanks
-Prachi
Re: Mutating Triggers [message #347805 is a reply to message #347787] Sun, 14 September 2008 02:13 Go to previous messageGo to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
Sir,

Please clarify this :
Make sure that lines of code do not exceed 80 characters when you format.

As I think,
1> All of you do not like using shortcuts.
2> You want a better explanation of our problems.
3> You actually want to see what we people tried.

If, the example we tried is very much short like say 6-7 lines, we think it is better to show it here instead of attaching a separate file.

Then, in such cases is it really possible to minimize the code up to 80 characters?

Thanks
-Prachi G.
Re: Mutating Triggers [message #347808 is a reply to message #347804] Sun, 14 September 2008 02:19 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
prachi.mgorwadkar wrote on Sun, 14 September 2008 09:09
I am sorry , I did not mention in the example the trigger as a ROW Level trigger. It must be a Row Level Trigger for using NEW and OLD.


Mutating table errors also only occur on ROW level triggers; hence you put in two flawed examples.
Why not simply copy-paste instead of typing new code full of errors and omissions?
Re: Mutating Triggers [message #347824 is a reply to message #347804] Sun, 14 September 2008 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does that say, I can actually change th value :NEW in BEFORE Update trigger? Like :NEW := 'abc?

Yes. (Of course, in a row level trigger)

Quote:
in such cases is it really possible to minimize the code up to 80 characters?

EACH LINE must not exceed 80 characters, not the whole code.

Regards
Michel
Re: Mutating Triggers [message #347951 is a reply to message #347824] Mon, 15 September 2008 03:04 Go to previous message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
Sir,

Thanks for the reply.

EACH LINE must not exceed 80 characters, not the whole code.

Next time I will definitely take care of.

Regards-
Prachi
Previous Topic: dbms_jobs
Next Topic: Multiple where conditions for a column in a single query
Goto Forum:
  


Current Time: Thu Nov 07 22:00:55 CST 2024