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

AFTER vs. BEFORE triggers

I have three tables. Let's name them as table A,B,C. I have written a before insert trigger on A (input table) which will do some validations and enter the date in B (temp table). Also, I have a before insert trigger on B that will do some processing and insert data in the output table C (output table). It's working fine, but now I want to implement a funactionality like this: after data is inserted in the output table C, its corresponding data has to be deleted from the input table A. How do we implement this concept?
So you insert into A, trigger inserts into B, trigger inserts into C and you want a trigger that will delete from A again. It gets pretty circular and this is the type of thing that leads to a mutating trigger. But this isn't so hard to do in your case. All you have to do is change your trigger on table A to an AFTER trigger, not a BEFORE trigger. That way, the row is there to be deleted.

For More Information

  • What do you think about this answer? E-mail the Edtior at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBA's 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, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.