2

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?

1 Answer 1

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
2
  • Yes,that is correct but what I need here is not this. I have no intention of solving this problem,I just want to know why when ROLLBACK in AFTER INSERT trigger then data in INSERTED table is removed.
    – Dunguyen
    Apr 10, 2020 at 16:01
  • Read the answer in the link I provided. I also inserted a section into the answer from the Q&A. It is by design. Apr 10, 2020 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.