If my for update trigger raise error, my update statement should fail

Question

To ensure version control, I created a For Update trigger on my table. I have two tables. Account table, step one Second, the Account history table, which is utilized in the trigger, has a column called Version. If any of my columns are modified, I have Version+1 written in the column, and the old record from the Account table will be inserted in the Account history in the trigger. Additionally, I have a trigger new condition written. The newer version ought to be grated. version, If I run an update query on my main (Account) table to perform negative testing while keeping the older version, I get a trigger-defined error, but my update statement still updates the Account table, even though it shouldn't. I need to add transaction(BEGIN TRY BEGIN CATCH TRAN) on my update?, If my trigger fails my update statement should fail

ALTER TRIGGER tr_AccountHistory
ON account
FOR UPDATE 
AS
BEGIN

        
    SELECT old.column
    FROM deleted

    SELECT new.Version
    FROM inserted

    SELECT old.Version FROM deleted
    
IF @Old_Version >= @New_Version
    BEGIN

    RAISERROR ('Improper version information provided',16,1);

    END    
    ELSE
    BEGIN

    INSERT INTO AccountHistory
    (
        insert column
    )
    VALUES
    (
        old.column
    );


    END
END



UPDATE account
SET id= 123456,
    Version = 1
WHERE id  =1

Answer 1

Instead of using RAISERROR, you should use THROW. This will respect XACT_ABORT and automatically rollback the transaction.

You also have other fatal flaws in your trigger:

• It expects there to be exactly one row modified. It may be multiple or zero rows.
• You have not declared any variables and are instead selecting back out to the client.
• Either way, you should just join the inserted and deleted tables by primary key.

CREATE OR ALTER TRIGGER tr_AccountHistory
ON account
FOR UPDATE 
AS

SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM inserted)   -- early bailout
    RETURN;
        
IF EXISTS (SELECT 1
    FROM inserted i
    JOIN deleted d ON d.YourPrimaryKey = i.YourPrimaryKey
    WHERE d.Version >= i.Version
)
    THROW 50001, 'Improper version information provided', 1;

INSERT INTO AccountHistory
(
  insert column
)
SELECT
  columsHere
FROM deleted;