Home » SQL & PL/SQL » SQL & PL/SQL » mutating table error
mutating table error [message #555912] Tue, 29 May 2012 21:46 Go to next message
kingpimp65
Messages: 1
Registered: May 2012
Junior Member
hello, I'm kind of new to database, and i want to create a trigger that will update a table when there is an insert or update.
i can't across this error that i don't even know what it means
"table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

this is my code please help,

CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/
Re: mutating table error [message #555913 is a reply to message #555912] Tue, 29 May 2012 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
you can not do SQL against table upon which Trigger is based.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: mutating table error [message #555916 is a reply to message #555912] Tue, 29 May 2012 23:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
To insert values into the row that caused the trigger to fire use :NEW.column_name as in the suggested trigger code below.

CREATE OR REPLACE TRIGGER set_date_end
  BEFORE INSERT OR UPDATE OF issued ON shares_amount
  FOR EACH ROW
BEGIN
  :NEW.date_end := SYSDATE;
END set_date_end;
/

Re: mutating table error [message #555917 is a reply to message #555912] Tue, 29 May 2012 23:16 Go to previous message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers

Regards
Michel
Previous Topic: How to view the rows in the table format using select query
Next Topic: Boolean variable in PL/SQL
Goto Forum:
  


Current Time: Fri Sep 19 03:44:17 CDT 2014

Total time taken to generate the page: 0.06772 seconds