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

Question

I give an example to show my problem. I created a table like this:

CREATE TABLE a
(
    id INT
)

I then created an AFTER INSERT trigger to not allow 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 rolled back (I know) and data in INSERTED table is also removed. Why is that?

If I change the AFTER INSERT trigger to an INSTEAD OF INSERT 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 deeply what happens inside trigger?

Welcome!, You said data in INSERTED table is also removed.Why is that? are you sure ? I executed your code and everything seems fine!, also change RAISERROR command to be RAISERROR(' I am In the Begin and End block after inserting into inserted table',12,1) just for more Clarification, also please notify the data is existing into inserted table only while executing DML commands then the data has gone. — ahmed abdelqader
inserted can contain 0, 1 or multiple rows. If someone inserts multiple rows where some of them have id 1 then a) What should the trigger do and b) Your current trigger only picks up one of the ids so may not react. Why do you not just have a check constraint instead? — Damien_The_Unbeliever
@Damien_The_Unbeliever, I just give an simple example to clarify my problem.My problem just is why data in INSERTED table is removed when ROLLBACK — Dunguyen

Cedersved Cedersved · Accepted Answer · 2020-04-10T08:20:11

This is the intended behaviour as far as I know. It's just that AFTER may be a bit misleading depending on how you look at it.

"The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected, the entire transaction automatically rolls back.".

https://msdn.microsoft.com/en-us/library/ms178110.aspx

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

1 Answers