|
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? |
|
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. 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)
(14 Jan '19, 10:50)
Baron
|
