CODEWITHSUNDEEP
oracle-database
Thomas Carlton
Thomas Carlton

Reputation: 5968

Is there any way to avoid trigger cascading in Oracle?

I have a the following tables :

Table2 has a trigger that performs some operations when a row is being deleted.

Is there anyway to avoid execution of the trigger in Table2 when the origin of delete operation is Table1 cascading ?

Thank you.

Upvotes: 2

Views: 383

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

I don't think there is an out-of-the-box way to do this, but you could make a package with a variable, which is set in one trigger and checked in the next.

Those package variables are session-specific, so you won't bother other sessions, although you should make sure that the variable is reset, otherwise stand-alone deletes may be recognised as cascaded deletes within the same session.

Roughly, it should look like this:

create package PKG_CASCADE as 
  V_YOURFLAG char(1);
end package;
/

create package body PKG_CASCADE as

begin
  V_YOURFLAG := 'N';
end;
/

create trigger TDB_Table1 before delete on Table1
begin
  begin
    PKG_CASCADE.V_YOURFLAG := 'Y';

    delete from Table2 .....;

    PKG_CASCADE.V_YOURFLAG := 'N';
  exception when others do
    PKG_CASCADE.V_YOURFLAG := 'N';
  end;
end;
/

create trigger TDB_Table2 before delete on Table2
begin
  if PKG_CASCASE.V_YOURFLAG <> 'Y' then
    -- Do you stuff.
  end if;
end;
/

I typed this without Oracle at hand, so please excuse me for any syntax errors.

For a more elaborate tutorial see:

Upvotes: 1

Related Questions