CODEWITHSUNDEEP
sqlsql-servert-sql
Vitalii
Vitalii

Reputation: 11081

MSSQL Update: output value before update

There is a table with IDU (PK) and stat columns. If first bit of stat is 1 I need to set it to 0 and run some stored procedure in this case only, otherwise I do nothing.

Here is the simple query for this 

DECLARE @s INT
-- get the current value of status before update
SET @s = (SELECT stat FROM  myTable
          WHERE IDU = 999999999)
-- check it first bit is 1
IF (@s & 1) = 1  
BEGIN
    -- first bit is 1, set it to 0
    UPDATE  myTable
    SET status = Stat & ~1
    WHERE IDU = 999999999
    -- first bit is one, in this case we run our SP
    EXEC SOME_STORED_PROCEDURE
END

But I'm not sure that this query is optimal. I heard about OUTPUT parameter for UPDATE query but I found how to get inserted value. Is there a way to get a value that was before insert?

Upvotes: 3

Views: 1917

Answers (3)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Yes, OUTPUT clause allows you to get the previous value before the update. You need to look at deleted and inserted tables.

DELETED

Is a column prefix that specifies the value deleted by the update or delete operation. Columns prefixed with DELETED reflect the value before the UPDATE, DELETE, or MERGE statement is completed.

INSERTED

Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

-- Clear the first bit without checking what it was

DECLARE @Results TABLE (OldStat int, NewStat int);

UPDATE myTable
SET Stat = Stat & ~1
WHERE IDU = 999999999
OUTPUT
    deleted.Stat AS OldStat
    ,inserted.Stat AS NewStat
INTO @Results
;

-- Copy data from @Results table into variables for comparison
-- Assumes that IDU is a primary key and @Results can have only one row

DECLARE @OldStat int;
DECLARE @NewStat int;

SELECT @OldStat = OldStat, @NewStat = NewStat
FROM @Results;

IF @OldStat <> @NewStat 
BEGIN
    EXEC SOME_STORED_PROCEDURE;
END;

Upvotes: 4

Stefanos Zilellis
Stefanos Zilellis

Reputation: 611

Regardless of optimal, this query is not 100% safe. This is because between SET @s =... and UPDATE myTable there is no guarantee the value of stat has not been changed. If this code runs multiple times it is possible to screw up if two cases execute deadly close for the same IDU. The first thread will do ok but the second one will not, since the first would change the stat after the second read it and before update it. A select statement does not lock beyond its own execution time even on SERIALIZABLE isolation.

To be safe, you need to lock the record BEFORE read it, and to do that you need an update statement, even fake:

DECLARE @s INT
BEGIN TRANSACTION
UPDATE myTable SET stat = stat WHERE IDU = 999999999 --now you row lock your row, make sure no other thread can move along
-- get the current value of status before update
SET @s = (SELECT stat FROM  myTable
          WHERE IDU = 999999999)
-- check it first bit is 1
IF (@s & 1) = 1  
BEGIN
    -- first bit is 1, set it to 0
    UPDATE  myTable
    SET status = Stat & ~1
    WHERE IDU = 999999999
    -- first bit is one, in this case we run our SP
-- COMMIT TRANSACTION here? depends on what SOME_STORED_PROCEDURE does
    EXEC SOME_STORED_PROCEDURE
COMMIT TRANSACTION --i believe here you release the row lock

I am not sure what you mean by "Is there a way to get a value that was before insert" because you only update and the only data, stat, you had already read from the old record regardless if you update or not.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

You could do this with an INSTEAD OF UPDATE Trigger.

Upvotes: 0

Related Questions