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?

asked 14 Jan, 06:11

Sarkis's gravatar image

Sarkis
434212843
accept rate: 0%


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.

permanent link

answered 14 Jan, 06:29

Volker%20Barth's gravatar image

Volker Barth
33.4k329482710
accept rate: 32%

edited 14 Jan, 06:33

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, 10:50) Sarkis
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×66

question asked: 14 Jan, 06:11

question was seen: 73 times

last updated: 14 Jan, 10:50