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

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

CodePudding user response：

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;