Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Cascade delete vs trigger when deleting on child table

705103Feb 11 2010 — edited Feb 11 2010
Hi,

I have a Cascade delete set up on 7 tables. Besides the firs one (which is the parent) , on all the "child" tables, o every INSERT/DELETE/UPDATE, I need to update the "parent" tables's LAST_UPDATED_DATE. I went ahead and created a trigger for this on each "child" table.

The issue i have is that when I try to cascade delete some record from the "parent" , it tells me that the "table is mutating" when the child trigger is in place, and i see this makes perfect sence, but how can i bypass this?



Kind regards
Alex

Comments

21205
What you could do instead of using the triggers and the cascade delete is create a packaged procedure which takes care of your actions.
So instead of deleting the "master" directly go through the packaged procedure. Instead of DML to the "detail" tables, use the INS - UPD - DEL procedure to manipulate the detail record and update the master record.

In essence create an API to do the necessary work for you, and use this API to interact with your tables.
705103
Thank you for your response. I will try to implement your solution.

Kind regards
Alex
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 11 2010
Added on Feb 11 2010
#11g, #apex, #delete, #table, #trigger
2 comments
846 views