Q
Problem solve Get help with specific problems with your technologies, process and projects.

Mutating table and recursive SQL errors with trigger

A table 'Tempinst ' with two columns:
a varchar2 (20);
b date ;

We insert a value for 'A' to tempinst table by insert statments in SQL Plus. A trigger has been written to insert/update value for B column by insert or update statement.

Create or replace  trigger dt_update_trig
after insert or update on tempinst
for each row
begin
if inserting then
insert into tempinst (b) values (sysdate);
else
update tempinst set b = sysdate;
end if;
end;
I have met some errors:
1. Mutating table
2. Recursive SQL error.

Please give me the solution to solve the problem.


The reason you are getting these errors is that you are attempting to insert or update into the same table that has caused the trigger to fire. You can simply change your trigger as follows to fix the problem:

create or replace  trigger dt_update_trig
before insert or update on tempinst
for each row
begin
   :new.b := sysdate ;
end;
/
First you make the trigger a BEFORE trigger instead of an after trigger. This allows you to use the :new attribute to modify column values before the actual insert or update occurs. And since you want to do the same thing (make column b = sysdate), regardless of whether you insert or update, you can remove the IF statement and just set column b = sysdate.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle error messages

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close