Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Trigger Error
Mutating Trigger Error [message #423548] Fri, 25 September 2009 04:51 Go to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
Basically I have a trigger that fires when a field in Table1 is updated. When this table is updated some values are inserted into Table2. This is simple.
However the problem comes because in my INSERT statement into Table2 I need to check some values in Table1 hence why I needed the case statement in the Insert statement.
This causes a mutating error to appear.
Is there a way around this? I need to check the values in Table1 in the trigger.
thanks
just ask if this doesnt make sense and I will try and explain it again!
Re: Mutating Trigger Error [message #423549 is a reply to message #423548] Fri, 25 September 2009 04:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do you just need to check values in the table_1 record that fired the trigger? If so, just reference them as :new.<column_name>

Otherwise there's probably a fundamental flaw in what you're trying to do - sounds harsh, but that's the underlying cause of most mutating table errors.

If you think what you're trying to do is sensible, you'll need to go for the standard Mutating Table solution:

A package to record the ids of the records inserted
A statement level before insert trigger to clear down the package
A row level before insert trigger to record the inserted ids in the package
A statement level After insert trigger to go through the inserted rows and do your insert processing for each one.
Re: Mutating Trigger Error [message #423551 is a reply to message #423548] Fri, 25 September 2009 05:08 Go to previous messageGo to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
Hi

Yeah I just need to check the values.
I tried using :NEW but it is still showing the same error message that the table is mutating etc.

Ive used
  SELECT (CASE WHEN (:NEW.field1) LIKE 'value' THEN '1' ELSE '2' END)
  FROM table1


this is in the INSERT statement

Thanks

anyway

[Updated on: Fri, 25 September 2009 05:10]

Report message to a moderator

Re: Mutating Trigger Error [message #423553 is a reply to message #423551] Fri, 25 September 2009 05:15 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Can you post the code of your trigger?

I suspect you just need following code in your trigger in table1

Insert into table2 (col1,col2....) values (CASE WHEN (:NEW.field1) LIKE 'value' THEN '1' ELSE '2' END,.....)

Re: Mutating Trigger Error [message #423554 is a reply to message #423553] Fri, 25 September 2009 05:24 Go to previous message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
Hi

I tried the code you suggested and it worked!
Thanks for the help.

Smile
Previous Topic: Selecting a random record from table
Next Topic: Case When statement in Insert statement
Goto Forum:
  


Current Time: Fri Dec 02 16:32:42 CST 2016

Total time taken to generate the page: 0.17191 seconds