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.