Trigger for invalidating the effect of Insert/Delete

[Baron]

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?

[VolkerBarth · Accepted solution]

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 · Accepted solution]

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)