SQL Immediately exit and rollback from "Instead Of" trigger if condition is met

Question

I have a table in one database that references IDs of a table in another database. I have set up Instead Of Insert/Update triggers to prevent from inserting IDs that do not exist in the other table. I want these triggers to abort the transaction if it finds an ID that does not exist, otherwise continue with the transaction.

Here is what I have tried:

CREATE TRIGGER [dbo].[tr_check_student_insert]
    ON [dbo].[student]
    INSTEAD OF INSERT
AS
BEGIN
    SET XACT_ABORT ON

    IF (EXISTS ... )
    BEGIN
        RAISERROR (N'[teacher_id] does not exist in [teacher]',11,1)
    END

    IF (EXISTS ... )
    BEGIN
        RAISERROR (N'[class_id] does not exist in [class]',11,1)
    END

    INSERT INTO [dbo].[student] ...
END

I have since discovered RAISERROR will not abort the transaction, even with SET XACT_ABORT ON, and the insert (or update) still occurs after the error is raised.

I know that I could wrap each condition in IF/ELSE IF statements, and call the insert on ELSE, but I am just wondering if there is a way to immediately exit and rollback the entire transaction instead.

Answer 1

You need to use BEGIN TRANSACTION and ROLLBACK in a TRY CATCH

Try something like this:

CREATE TRIGGER [dbo].[tr_check_student_insert]
    ON [dbo].[student]
    INSTEAD OF INSERT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT ON;

    IF (EXISTS ... )
    BEGIN
        RAISERROR (N'[teacher_id] does not exist in [teacher]',11,1)
    END

    IF (EXISTS ... )
    BEGIN
        RAISERROR (N'[class_id] does not exist in [class]',11,1)
    END

    INSERT INTO [dbo].[student] ...

;
COMMIT
END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0
     ROLLBACK

    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
       RETURN;
END CATCH

END