Forum Stats

  • 3,838,084 Users
  • 2,262,326 Discussions
  • 7,900,498 Comments

Discussions

Cascade delete vs trigger when deleting on child table

705103
705103 Member Posts: 17
edited Feb 11, 2010 4:00AM in SQL & PL/SQL
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

Answers

  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    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
    705103 Member Posts: 17
    Thank you for your response. I will try to implement your solution.

    Kind regards
    Alex
This discussion has been closed.