cancel
Showing results for 
Search instead for 
Did you mean: 

Trigger for invalidating the effect of Insert/Delete

Baron
Participant
0 Kudos
1,126

Is there any way to invalidate the effect of a Delete Statement? I want to have a before_delete trigger on a table, so that this trigger allows the delete only for specific rows with using some custom criteria. The decision making is easy (referencing old as old_rec..... if old_rec.Column1 = '1' then....), but the question is, how can I obstruct the delete statement to take place?

The same trigger I need for before_insert in order to invalidate an insert statement (and I dont want to implement an undo effect which means that I should implement an after_insert trigger and delete the newly inserted rows). An available option is to use INSTEAD OF Trigger, but this has a drawback that the 'ON EXISTING' Clause will be no more available on the table!

Any ideas?

Accepted Solutions (1)

Accepted Solutions (1)

VolkerBarth
Contributor
0 Kudos

You can use the ROLLBACK TRIGGER statement within a trigger to undo its operation.

The optional WITH RAISERROR clause allows to specify an error code and message text to return to the caller. (Without that clause, I guess that the according operation is undone without effect on the surrounding transaction, i.e. the transaction itself would not be rollbacked.)

Here's an old sample from Breck's blog.

Baron
Participant
0 Kudos

Thank you very much for the help. The example of Breck has worked, despite the ROLLBACK TRIGGER Statement in a BEFORE DELETE Trigger is (the delete is undoed before it already bein implemented)

Answers (0)