Writing data integrity code in TRIGGER objects is generally is pretty simple. Typically you write a query to see “is there a row in inserted/deleted that is not set up as desired for the current transaction?” and no bad data is found, keep moving. And because MOST of the time, you would only use a TRIGGER object where transactions are quick coupled with the reality that a lot of the issues like I will describe would have to occur within milliseconds… concurrency issues seem to rarely occur. But…anything that isn’t impossible, is possible. Because of the rarity of the issue, if your code isn’t running a life of death system, when the anomalies happen, they are brushed off as “just something weird occurred”.
This blog will cover one such weird occurrence that can happen when your trigger validation code queries another table in the default isolation level. You expect that your data is protected until your transaction ends, but it may not be.
As an example scenario, consider something like a Payment being processed for a Sale. For example, say the payment was set to ‘Processed’, but the user asked for a refund. The users sets the Payment status to ‘Refunded’ and expects that there are no Sale rows recorded for that Payment. (The sale could be a shipment, etc… Admittedly this scenario is very thin, and is really not a life or death scenario, but it should do for an example scenario to make the concurrency concerns clear.) Yet, somehow when the process has completed, it turns out that a Sale has been recorded.
To demonstrate the issue, I will first create two barebones tables, one for the Payment, and one for the Sale:
CREATE SCHEMA Example ;
GO
CREATE TABLE Example .Payment
(
PaymentId int CONSTRAINT PKPayment PRIMARY KEY ,
StatusCode char (10 ) NOT NULL
)
–insert a Payment row
INSERT INTO Example .Payment ( PaymentId , StatusCode )
VALUES (1 , ‘Processed’ ), (2 , ‘Processed’ );
GO
CREATE TABLE Example .Sale
(
SaleId int CONSTRAINT PKSale PRIMARY KEY ,
PaymentId int NOT NULL
CONSTRAINT FKSale $ References $ PaymentId
REFERENCES Example .Payment ( PaymentId )
);
Next, I created a TRIGGER object to check to see if the Payment is Processed before inserting the Sale row:
CREATE TRIGGER Example . Sale $ InsertCheckStatusTrigger
ON Example . Sale
AFTER INSERT AS
BEGIN
SET NOCOUNT ON ; --to avoid the rowcount messages
SET ROWCOUNT 0 ; --in case the client has modified the rowcount
–use inserted for insert or update trigger, deleted for update or
–delete trigger count instead of @@ROWCOUNT due to merge behavior that
–sets @@ROWCOUNT to a number that is equal to number of merged rows,
–not rows being checked in trigger
DECLARE @ msg varchar (2000 ), --used to hold the error message
–use inserted for insert or update trigger, deleted for update or
–delete trigger count instead of @@ROWCOUNT due to merge behavior that
–sets @@ROWCOUNT to a number that is equal to number of merged rows,
–not rows being checked in trigger
@ rowsAffected int = ( SELECT COUNT ( * ) FROM inserted );
–no need to continue on if no rows affected
IF @ rowsAffected = 0 RETURN ;
BEGIN TRY
–[validation section]
< strong > IF EXISTS ( SELECT *
FROM inserted
JOIN Example . Payment
ON inserted . PaymentId = Payment . PaymentId
WHERE Payment . StatusCode <> ‘Processed’ ) --we already know PaymentId exists
</ strong > BEGIN
IF @ rowsAffected = 1 --custom error message for single row
SELECT @ msg = CONCAT ( 'The status code of PaymentId ’ , inserted . PaymentId ,
’ is not ‘‘Processed’’.’ )
FROM inserted ; --and/or deleted, depending on action
ELSE
SELECT @ msg = ‘A PaymentId in the inserted Sale rows is not Processed’ ;
–in the TRY . . . CATCH block, this will redirect to the CATCH
THROW 50000 , @ msg , 16 ;
END ;
–[modification section]
END TRY
BEGIN CATCH
IF @@ trancount > 0
ROLLBACK TRANSACTION ;
THROW ; --will halt the batch or be caught by the caller’s catch block
END CATCH ;
END ;
Now, a few basic tests:
–Works, real Payment, Payment is Processed
INSERT INTO Example .Sale ( SaleId , PaymentId )
VALUES (1 ,1 )
GO
INSERT INTO Example .Sale ( SaleId , PaymentId )
VALUES (2 ,3 ) --not a real PaymentId
Hence the error message comes from the FOREIGN KEY constraint:
Msg 547, Level 16, State 0, Line 107
The INSERT statement conflicted with the FOREIGN KEY constraint "FKSale$References$PaymentId". The conflict occurred in database "tempdb", table "Example.Payment", column 'PaymentId'.
Now we set the Payment to Refunded and try again.
UPDATE Example . Payment
SET Payment . StatusCode = ‘Refunded’
WHERE PaymentId = 1 ;
Execute the following statement.
INSERT INTO Example .Sale ( SaleId , PaymentId )
VALUES (3 ,1 );
#blogs #uncategorized #concurrency #t-sql #triggers #sql
