CODEWITHSUNDEEP
sqlsql-servert-sql
John Smith
John Smith

Reputation: 6259

Rollback in Trigger

I created a trigger check_id. With that trigger I want to rollback an Insert Statement on User in case the User.Id = 4

ALTER TRIGGER check_id on USER
FOR INSERT
AS
 BEGIN

   IF 4 = (SELECT Id from inserted)
    BEGIN
        ROLLBACK;
    END
 END

However when I do a Insert on User with Id = 4 like this:

INSERT INTO USER (Id, Name)
VALUES (4, 'Emmanuel')

I get an error:

Rollback not available in an atomic process

What do I wrong? How can I rollback a INSERT within a trigger?

Upvotes: 0

Views: 1027

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

Use check constraint instead:

SqlFiddle

 ALTER TABLE [user] ADD CONSTRAINT chk_user CHECK (id <> 4);

Upvotes: 4

Related Questions