CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
Vague
Vague

Reputation: 2280

Iterate inserted in trigger

This SQL Server trigger works correctly for a single insert

CREATE TRIGGER [dbo].[DebitSale]  
ON [dbo].[OrderDetails]  
AFTER INSERT, UPDATE   
AS  
DECLARE @OrderId bigint
DECLARE @OrderNr int
DECLARE @TaxRate decimal(9,2) 
DECLARE @Tax decimal(9,2) 
DECLARE @Total decimal(9,2) 
DECLARE @syncRate decimal(9,8)
DECLARE @Net decimal(9,2) 
DECLARE @OldNet decimal(9,2) 
DECLARE @ExactFee decimal(9,2) 
DECLARE @Fee decimal(9,2) 
DECLARE @JobId bigint
DECLARE @ItemCode nvarchar(35)
IF EXISTS ( SELECT 0 FROM inserted )
BEGIN
    SELECT  @syncRate = SyncRate
    FROM    dbo.[UserAccount]
    WHERE   Id = 0

    SELECT @Net = i.NrUnits * i.UnitNet from inserted i
    SELECT @OldNet = d.NrUnits * d.UnitNet from deleted d
    IF(@Net=@OldNet)
    BEGIN
        RETURN
    END
    SELECT @OrderId = i.OrderId, @JobId = i.JobId, @ItemCode = i.ItemCode FROM inserted i
    SELECT @OrderNr = OrderNr, @TaxRate = TaxRate FROM Orders WHERE OrderId = @OrderId

    SET @Tax = @Net * @TaxRate / 100
    SET @Total = @Net + @Tax
    SET @ExactFee = @Total * @syncRate
    SET @Fee = FLOOR(@ExactFee * 100)/100

    IF(@Fee >= 0.01)
    BEGIN
    IF @JobId IS NULL
        INSERT INTO SubscriptionTransactions 
        (ItemValue, OrderNr, TransactionCredit, TransactionDate, TransactionDebit, TransactionDescription, TransactionType)
        VALUES (@Total, @OrderNr, 0, GetDate(), @Fee, @ItemCode, 'Item') 
    ELSE
        INSERT INTO SubscriptionTransactions 
        (ItemValue, OrderNr, TransactionCredit, TransactionDate, TransactionDebit, TransactionDescription, TransactionType)
        VALUES (@Total, @OrderNr, 0, GetDate(), @Fee, @ItemCode, 'Job') 
    END
END

However we sometimes get multiple rows in inserted so I included a loop like this

ALTER TRIGGER [dbo].[DebitSale]  
ON [dbo].[OrderDetails]  
AFTER INSERT, UPDATE   
AS  
BEGIN
    DECLARE @OrderId bigint
    DECLARE @OrderDetailId bigint
    DECLARE @OrderNr int
    DECLARE @TaxRate decimal(9,2) 
    DECLARE @Tax decimal(9,2) 
    DECLARE @Total decimal(9,2) 
    DECLARE @syncRate decimal(9,8)
    DECLARE @Net decimal(9,2) 
    DECLARE @OldNet decimal(9,2) 
    DECLARE @ExactFee decimal(9,2) 
    DECLARE @Fee decimal(9,2) 
    DECLARE @JobId bigint
    DECLARE @ItemCode nvarchar(35)

    SELECT  @syncRate = SyncRate
    FROM    dbo.[UserAccount]
    WHERE   Id = 0

    DECLARE @current INT = 0;
    DECLARE @InsertedCount int
    SELECT @InsertedCount =  Count(*) FROM inserted
    DECLARE @Thing nvarchar(max)
    SELECT @Thing ='InsertedCount ' + CAST(@InsertedCount AS VARCHAR)
    INSERT INTO DebugTable (DebugColumn) VALUES (@Thing)


    WHILE @current <= @InsertedCount
    BEGIN
        SELECT @Thing ='current ' + CAST(@current AS VARCHAR)
        INSERT INTO DebugTable (DebugColumn) VALUES (@Thing)
        SELECT @OrderDetailId AS OrderDetailId from inserted i
        ORDER BY OrderDetailId
        OFFSET @current ROWS   
        FETCH NEXT 1 ROWS ONLY  

        SET @current = @current + 1;
        SELECT @Net = NrUnits * UnitNet FROM inserted i WHERE i.OrderDetailId= @OrderDetailId
        SELECT @OldNet = d.NrUnits * d.UnitNet from deleted d where d.OrderDetailId= @OrderDetailId
        IF(@Net=@OldNet)
            CONTINUE 

        SELECT @OrderId = i.OrderId, @JobId = i.JobId, @ItemCode = i.ItemCode FROM inserted i WHERE i.OrderDetailId= @OrderDetailId
        SELECT @OrderNr = OrderNr, @TaxRate = TaxRate FROM Orders WHERE OrderId = @OrderId

        SET @Tax = @Net * @TaxRate / 100
        SET @Total = @Net + @Tax
        SET @ExactFee = @Total * @syncRate
        SET @Fee = FLOOR(@ExactFee * 100)/100
        IF(@Fee < 0.01)
            CONTINUE 

        IF (@JobId IS NULL)
            INSERT INTO SubscriptionTransactions 
            (ItemValue, OrderNr, TransactionCredit, TransactionDate, TransactionDebit, TransactionDescription, TransactionType)
            VALUES (@Total, @OrderNr, 0, GetDate(), @Fee, @ItemCode, 'Item') 
        ELSE
            INSERT INTO SubscriptionTransactions 
            (ItemValue, OrderNr, TransactionCredit, TransactionDate, TransactionDebit, TransactionDescription, TransactionType)
            VALUES (@Total, @OrderNr, 0, GetDate(), @Fee, @ItemCode, 'Job')
    END
    INSERT INTO DebugTable (DebugColumn) VALUES (@ItemCode)
END

This is a remote server that services a Web API. I don't have access to remote debugging so I've put some debug code in that logs a couple of lines to a DebugTable. The trigger writes to DebugTable if I execute an insert statement in SSMS, but doesn't update Transactions.

The Web API executes a merge statement in which case nothing is written to DebugTable and Transactions are not updated. I'm not sure if the new trigger is even firing.

I know there is a lot of TSQL here, but given the original trigger seems to work for a single insert, and that trigger is unchanged except for wrapping it in a loop, I assume I've got the loop wrong.

I expect some will spurn the loop, but in mitigation, there are rarely more than 2 rows inserted at a time.

How do I loop through inserted in a trigger?

Upvotes: 0

Views: 501

Answers (1)

Dale K
Dale K

Reputation: 27201

Your main insert should be something like the following i.e. there is no need for a loop. One should always aim for set based operations in SQL if at all possible. The key points are:

  • JOIN the relevant tables together
  • use CROSS APPLY to avoid having to repeat calculations
  • use the WHERE clause to exclude cases you are currently using CONTINUE for.

I've tried to match your logic as best I can. But without being able to test it I can't be sure it works. You will want to run it through a few tests to be sure its working exactly as you wish.

And I leave the debugging/logging code as an exercise for you.

    INSERT INTO dbo.SubscriptionTransactions (ItemValue, OrderNr, TransactionCredit, TransactionDate, TransactionDebit, TransactionDescription, TransactionType)
        SELECT
            T.Total
            , O.OrderNr
            , 0
            , GetDate()
            , F.Fee
            , I.ItemCode
            , CASE WHEN I.JobId IS NULL THEN 'Job' ELSE 'Item' END
        FROM Inserted I
        -- Deleted won't exist for an insert
        LEFT JOIN Deleted D on D.OrderDetailId = I.OrderDetailId
        INNER JOIN Orders O on O.OrderId = I.OrderId
        CROSS APPLY (
            VALUES (I.NrUnits * I.UnitNet, D.NrUnits * D.UnitNet)
        ) N (Net, OldNet)
        CROSS APPLY (
            VALUES (N.Net * (1.0 + N.Net * O.TaxRate / 100))
        ) T (Total)
        CROSS APPLY (
            VALUES (FLOOR(T.Total * @SyncRate * 100)/100)
        ) F (Fee)
        WHERE (N.Net != N.OldNet OR D.OrderDetailId IS NULL) -- First continue condition
        AND Fee >= 0.01; -- Second continue condition

Upvotes: 3

Related Questions