SQL Server does not rollback trigger transaction to savepoint

Question

I am facing an issue trying to set up a transaction within a trigger of my view. Here is my DDL setup:

CREATE TABLE entity1 (
    id INT NOT NULL IDENTITY PRIMARY KEY,
    attr1 INT NOT NULL,
    attr2 INT NOT NULL
);
GO

CREATE TABLE entity2 (
    entity1_id INT NOT NULL FOREIGN KEY REFERENCES entity1(id),
    attr3 INT NOT NULL,
    attr4 INT NOT NULL
);
GO

CREATE VIEW my_view AS
SELECT attr1, attr2, attr3, attr4
FROM entity1 AS e1 
INNER JOIN entity2 AS e2 
ON e1.id = e2.entity1_id;
GO

CREATE TRIGGER tg_my_view_ins ON my_view
INSTEAD OF INSERT AS
BEGIN
    BEGIN TRY
        SAVE TRANSACTION here; -- checkpoint
        INSERT INTO entity1 (attr1, attr2) 
        SELECT attr1, attr2 FROM inserted;
        INSERT INTO entity2 (entity1_id, attr3, attr4) 
        SELECT SCOPE_IDENTITY(), attr3, attr4 FROM inserted;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION here; -- rollback to checkpoint in case on an error
    END CATCH
END
GO

As you can see, I make a savepoint while in the trigger, and rollback in case of any errors (I assume that constraint errors are also handled by TRY/CATCH blocks). The problem is, when I execute bad inserts within the transaction, trigger error handling block does not rollback:

BEGIN TRY
    BEGIN TRANSACTION;

        -- successful insert
        INSERT INTO my_view (attr1, attr2, attr3, attr4) VALUES (1,2,3,4);
        SELECT * FROM entity1; -- one entity

        -- i wrap the bad insert into try/catch so the error is discarded, 
        -- but still rolled back
        BEGIN TRY
            INSERT INTO my_view (attr1, attr2, attr3) VALUES (3,2,1);
        END TRY
        BEGIN CATCH
        END CATCH;

        SELECT * FROM entity1; -- should only have one entity, but has two
    ROLLBACK; -- discard the whole transaction
END TRY
BEGIN CATCH
    ROLLBACK; -- discard the whole transaction in case of any errors
END CATCH;

I do not seem to be able to set up the trigger the way it would not create orphan records in case of an error. I have tried using BEGIN TRANSACTION here and COMMIT TRANSACTION here within my trigger instead of SAVE TRANSACTION here as well, with no luck. What is the correct way to handle constraint erros within triggers?

The execution setup I would like to keep the way it is, if possible. I create and rollback the transaction for testing purposes. I wrap the bad insert into a try/catch block to discard the error I know should happen.

Answer 1

This seemingly confused behaviour can be made clear by adding error logging into your catch blocks. The following modification of your test code adds error logging (and some other improvements), which shows what actually happens in the process:

begin try
  begin transaction;

  INSERT INTO dbo.my_view (attr1, attr2, attr3, attr4) VALUES (1,2,3,4);
  SELECT * FROM dbo.entity1;

  BEGIN TRY
  INSERT INTO dbo.my_view (attr1, attr2, attr3) VALUES (3,2,1);
  END TRY
  BEGIN CATCH
  -- Logging - inner CATCH
  select 'Inner', @@trancount, error_number(), error_message(), error_procedure(), error_line();
  END CATCH;

  select * from dbo.entity1;
  rollback;

end try
begin catch

  -- Logging - outer CATCH
  select 'Outer', @@trancount, error_number(), error_message(), error_procedure(), error_line();

  -- Conditional rollback, because some errors always terminate the transaction
  if @@trancount > 0
    rollback;

end catch;

If you run this code with your trigger intact, you will see an error caught by the inner CATCH:

3931

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

Searching by the error number leads to this post with a similar question. In his answer, Rutzky shows that the culprit of this behaviour is the XACT_ABORT session option which is apparently set to ON for triggers by default. If your intent is to pursue your trigger-based architecture, then turning this option off inside your trigger will help:

create or alter trigger dbo.tg_my_view_ins
on dbo.my_view
instead of insert as

-- Implicitly set to ON in triggers by default; makes error handling impossible
set xact_abort off;

begin try
  save transaction here;

  INSERT INTO dbo.entity1 (attr1, attr2) 
  SELECT attr1, attr2 FROM inserted;

  INSERT INTO dbo.entity2 (entity1_id, attr3, attr4) 
  SELECT e.id, attr3, attr4
  FROM inserted i
    -- The actual JOIN condidions should reference a natural key in the master table.
    -- This is just an example.
    inner join dbo.entity1 e on e.attr1 = i.attr1 and e.attr2 = i.attr2;

end try
begin catch

  if @@trancount > 0
    rollback transaction here;

end catch;
return;
GO

(Again, I have corrected several other issues with your code.)