Can I fail the current transaction from within a trigger?

Posted on

Question :

Table DDL:

CREATE TABLE trigtest(a INTEGER IDENTITY(1,1) PRIMARY KEY, b INT)

I currently have

CREATE TRIGGER MaxDelete ON trigtest INSTEAD OF DELETE AS
IF @@rowcount < 1000
DELETE trigtest  WHERE a IN (SELECT a FROM deleted)

However, it requires me to write the last line in a customized manner for every table I apply it to

Instead of the above, is it possible to write something of the sort:

IF @@rowcount > 1000 (OR SELECT COUNT(*) FROM DELETED)
Kill statement
ELSE
Continue statement

Answer :

IF @@ROWCOUNT > 0
BEGIN
   ROLLBACK TRANSACTION;
END
ELSE
BEGIN
  ... continue ...
END

Leave a Reply

Your email address will not be published. Required fields are marked *