0

I have a trigger FOR INSERT and I need to stop the insertion by raising a error.

RAISERROR

will show the error message but is there is a way to halt the action any other method without transactions ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sudantha
  • 15,684
  • 43
  • 105
  • 161
  • 1
    The proper way to do this **IS** to use transactions and then in an error case, issue a `ROLLBACK TRANSACTION` - there's really no other means to "stop" an INSERT inside a trigger... – marc_s Feb 06 '11 at 18:05
  • if im having the tranasction inside the FOR triggerthe value is already there in the inserted table and already inserted to the table r8? – Sudantha Feb 06 '11 at 18:11

1 Answers1

5

You have the trigger in the wrong place.

You need an INSTEAD OF trigger instead (no pun intended) to control it. A FOR INSERT trigger occurs AFTER the insert, so the horse has long bolted.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • To say nothing of `AFTER INSERT` triggers... +1 for the INSTEAD OF solution. – Andriy M Feb 06 '11 at 19:03
  • In an AFTER INSERT trigger, you can always rollback the transaction in an error case, and thus undo the INSERT... – marc_s Feb 06 '11 at 19:13
  • 1
    @marc_s: I was only pondering over the idea of a horse having long bolted in a FOR INSERT trigger and what could be said about it (the horse) regarding an AFTER INSERT one. And it's only now that I've discovered that in SQL Server they are the same kind of trigger! I've never used the `AFTER INSERT` syntax, only `FOR INSERT`, and while having heard of the former, I always used to think it was a different concept (that is, the operation could not be revoked in the AFTER INSERT trigger). Well, it's never too late to learn. – Andriy M Feb 07 '11 at 08:40