Sql-server – ROLLBACK make data in INSERTED table is removed in AFTER INSERT TRIGGER

insertrollbacksql servertransactiontrigger

I give an example to show my problem. I create a table as the following:

CREATE TABLE a
(
  id INT
)

I create a AFTER INSERT trigger to don't allow to insert id=1 into table "a"

CREATE TRIGGER [dbo].[insert_a]
ON [dbo].[a] AFTER INSERT 
AS
BEGIN

DECLARE @id INT
SELECT @id=id FROM inserted
IF @id=1
 BEGIN
  RAISERROR('1',12,1)
  ROLLBACK;
 END
SELECT * FROM inserted
END

Then I insert id=1 into table "a":

INSERT INTO a VALUES(1)

I get nothing from INSERTED table.

I realize that when I ROLLBACK then:

  • the data in table "a" was ROLLBACK (I know)

  • data in INSERTED table is also removed.Why is that?

If I change AFTER INSERT into INSTEAD OF INSERT in trigger.

ALTER TRIGGER [dbo].[insert_a]
ON [dbo].[a] INSTEAD OF INSERT 
AS
BEGIN

DECLARE @id INT
SELECT @id=id FROM inserted
IF @id=1
 BEGIN
  RAISERROR('1',12,1)
  ROLLBACK
 END

SELECT * FROM inserted
END

INSERT INTO a VALUES(1)

Then I get the result:

id
1

That means data in INSERTED table is not removed though have been ROLLBACK.

Help me explain?

Best Answer

Your row is not being inserted in either case.

For after insert trigger rollback is part of the transaction. See this Q&A for details.

SQL Server - After Insert/ For Insert - Rollback

Quote from the above Q&A.

You're inserting one (or multiple) row into your table. Then - still inside the transaction - the AFTER INSERT trigger runs and checks certain conditions - typically using the Inserted pseudo table available inside the trigger, which contains the rows that have been inserted.

If you call ROLLBACK TRANSACTION in your trigger, then yes - your transaction, with everything it's been doing, is rolled back and it's as if that INSERT never happened - nothing shows up in your database table.

For instead of trigger the id value of 1 you are seeing is not from the table. It is from the code within your trigger. This value of 1 is coming from:

SELECT * FROM inserted

Run this code and you will not get a record back.

ALTER TRIGGER [dbo].[insert_a]
ON [dbo].[a] INSTEAD OF INSERT 
AS
BEGIN

DECLARE @id INT
SELECT @id=id FROM inserted
IF @id=1
 BEGIN
  RAISERROR('1',12,1)
  ROLLBACK
 END

SELECT * FROM inserted
END
GO
INSERT INTO a VALUES(1);
GO
SELECT * FROM dbo.a;
GO
Related Question