ROLLBACK make data in INSERTED table is removed in AFTER INSERT TRIGGER

Question

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?

Answer 1

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