Skip running trigger during cascade delete

oracletrigger

I have a legacy application where I have two tables X and Y. Table Y has a trigger that performs an action automatically on delete. There is also a foreign key with cascade delete defined between X and Y (such that entries in Y are deleted when X is deleted).

Is there any way to have the trigger NOT run during a cascade delete?

The only way I currently know that this could be done is to create a package level variable to use as a flag that the trigger would check to determine if it should run. I would then change the value of the flag in my stored procedure before and after running the delete on table X.

I was hoping to find something a bit cleaner where you could simply tell oracle not to execute the trigger if it is a cascade delete.

Best Answer

Short of disabling the trigger before executing your DELETE statement (which would affect all sessions and would only be appropriate if you are doing an off-hours bulk delete), setting a package variable (or defining your own context and setting a context variable) that your trigger reads is likely the cleanest option without reworking the existing application.

When you have an action that generally needs to be done when a row in Y is deleted but not when Y is deleted because of the cascade delete, that is probably not an action that should be in a trigger in the first place. It ought to be part of the stored procedure (or other API) that allows you do do "normal" deletes on Y. Of course, that sort of refactoring may not be possible with a legacy application so you may be stuck with some of the less elegant workarounds.

Related Solutions

Sql-server – Is it possible to raiserror within a For Delete Trigger that rolls back the deletes

In order for that to work as you've coded it above, you need a rollback after your RAISERROR or, as the comments correctly state, wrap in a TRY/CATCH.

By itself, it's not going to be treated the same as an actual statement or batch terminating error. For example, this will work the way you expect:

create table T1 ( i1 int  );
create table t2 (i2 int primary key);
go

create trigger T1_ForDelete on T1
for delete
as
    insert into t2 (i2)
    SELECT i1 
    FROM DELETED
go

insert into T1 (i1) values (1);
insert into t2 (i2) values (1);

delete from T1;
go

SELECT * from t1

In the above code the trigger will error, and cause the delete (outer) transaction to rollback. By itself, RAISERROR is only really transferring control and/or sending messages up the stack (so to speak)

BOL describes this behavior:

When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

Outside the scope of any TRY block.

With a severity of 10 or lower in a TRY block.

With a severity of 20 or higher that terminates the database connection.

So RAISERROR outside the scope of a TRY block simply returns the error to the caller and is not treated as a statement-terminating error regardless of the severity you define.

Postgresql – Delay Trigger Execution

No, deferred trigger execution will not help. It only defers triggers until the end of the transaction.

PostgreSQL doesn't have a built-in task/job scheduler, either, so you'll need to do this in your app.

In the trigger, insert a row into a table that keeps track of which entries should be deleted and when. Scan that table every few minutes and, in a single transaction, records that're past their expiry time and their corresponding entry in the delete queue table.

Best Answer

Related Solutions

Sql-server – Is it possible to raiserror within a For Delete Trigger that rolls back the deletes

Postgresql – Delay Trigger Execution

Related Question