Skip to Main Content
  • Questions
  • Can be differentiate cascade delete or statement(delete from query) inside the table trigger

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, AKASH.

Asked: July 04, 2016 - 6:25 am UTC

Last updated: July 04, 2016 - 12:21 pm UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,
i have question about cascade delete, How its work internally and how to differentiate that row by delete from delete statement on child table or row deleted by a cascade delete.

because i am facing problem and try to solve Mutating error.

Thanks Tom.

and Connor said...

See an example here

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:469621337269

for both avoiding mutating tables, and also, using a package variable to indicate information to the trigger.

For example, in your case, when deleting from the *parent*, you could put a trigger on the parent:

- set pkg_variable to "parent_initiated_the_delete"

and in a trigger on the child, have

if pkg_variable = "parent_initiated_the_delete" then
-- take some action
else
-- take different action
end if;

Hope this helps.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 04, 2016 - 7:14 am UTC

Thanks Connor McDonald,
for use full response for my question,

right now i am using this but just thinking about that, is any way to recognize this cascade delete or delete query inside trigger or not.

so can avoid extra trigger and global variable or it's not possible to differentiate this.

Connor McDonald
July 04, 2016 - 12:21 pm UTC

I would imagine with some queries to the v$ views you could make some inferences, eg (the below not tested btw):

use v$session to examine sql_id and prev_sql_id to see if either contains a delete to the parent.

But that seems a overly convoluted means of doing so. Before heading down that path, I'd be asking why you need to distinguish between them ?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library