Duplicate record inserted - why trigger is not preventing it?

Question

I have two tables: DebitIUVs and RPTs. I have record inside DebitIUVs which is with state 118001. My business logic must insert new line inside RPTs table. Inside RPTs table there is additional business logic and updates state of in parent table DebitIUVs. The idea is to prevent inserting new row in the RPTs table when the state is changed to be different from 118001 (while changes are in transaction in the trigger). Calls are executed via WEB Service.

For unknown reason we have 2 calls in the same time which cases 2 rows inserted in RPTs table. The code seems logically correct. Also we have heavy load in the database (sometimes it reaches 100000 transactions per day).

Any idea why this can happened?

Simplified code:

CREATE TABLE [dbo].[DebitIUVs](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDIUVState] [int] NOT NULL,
    CONSTRAINT [PK_DebitIUVs_ID] PRIMARY KEY NONCLUSTERED ([ID] ASC)
);

CREATE TABLE [dbo].[RPTs](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDDebitIUV] [int] NOT NULL,
    [IDRPTState] [int] NOT NULL CONSTRAINT [DF_RPTs_IDRPTState] DEFAULT ((120001)),
    [PaymentRequestID] [varchar](35) NULL,
    CONSTRAINT [PK_RPTs_ID] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [UK_RPTs_PaymentRequestID] UNIQUE NONCLUSTERED ([PaymentRequestID] ASC)
);
GO

CREATE TRIGGER dbo.trg_aiu_RPTs_RI
    ON dbo.RPTs
    FOR INSERT, UPDATE 
AS 
BEGIN
    --if no rows are affected - return
    IF ISNULL((SELECT COUNT(ID) FROM inserted), 0) = 0 RETURN;

    DECLARE @dtNow DATETIME = GetDate();

    SET NOCOUNT ON;
    UPDATE RPT SET
        PaymentRequestID =
            CONVERT(VARCHAR(10), @dtNow, 112) + REPLACE(CONVERT(VARCHAR(10), @dtNow, 108), ':', '') +
            RIGHT(REPLICATE('0', 10) + CAST(ins.ID AS VARCHAR(10)), 10)
    FROM inserted ins
        INNER JOIN dbo.RPTs RPT ON
            (RPT.ID = ins.ID)
    WHERE ins.PaymentRequestID IS NULL;

    IF UPDATE(IDRPTState)
    BEGIN
        UPDATE DIUV SET
            DIUV.IDIUVState = 
                CASE
                    WHEN ins.IDRPTState = 120101 THEN 118002
                    WHEN ins.IDRPTState = 120201 THEN 118003
                    ELSE DIUV.IDIUVState
                END
        FROM
            inserted ins
            LEFT JOIN deleted del ON del.ID = ins.ID
            INNER JOIN dbo.DebitIUVs AS DIUV ON DIUV.ID = ins.IDDebitIUV
        WHERE (del.ID IS NULL) OR (del.IDRPTState <> ins.IDRPTState);
    END;
END;
GO

CREATE PROCEDURE dbo.sproc_1
    @IDIUV INT,
    @IDIUVState INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION InsertDebit;
    INSERT INTO dbo.RPTs (IDRPTState, IDDebitIUV)
    SELECT @IDIUVState, IUV.ID
    FROM dbo.DebitIUVs IUV
    WHERE
        (IUV.ID = @IDIUV) AND
        (IUV.IDIUVState <> 118002);
    COMMIT TRANSACTION InsertDebit;
END;
GO

INSERT INTO DebitIUVs (IDIUVState) VALUES (118001);
EXEC dbo.sproc_1 1, 120101;

Answer 1

If you suspect this to be as a result of 2 transactions occurring nearly simultaneously, then it would appear to perhaps be them overlapping and the first not having committed before the second queries the RPTs table.

This will be down to your Transaction Isolation Levels for the queries. Top validate this you could include SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in the Stored Procedure to force the second to wait until the first commits before operating.