=> Trigger to copy duplicate rows into a new table where val >26

From: Rhonda Fischer (anonymous_at_discussions.microsoft.com)
Date: 04/07/04 

Date: Wed, 7 Apr 2004 08:06:09 -0700

Hello,

I am attempting to create the following trigger:

=============================================================================
CREATE TRIGGER trgLoadExceedTrlCapacity ON [tblOrders]
FOR INSERT, UPDATE, DELETE
AS
        /* The purpose of this trigger is to ensure that no entries have in excess of 26 pallet loads */
             /* The Supermarket may order more than 26 loads of produce but the Trailer can only manage 26 loads */
             /* so extra lines are inserted into the new table tblSplitOrders with a maximum of 26 loads */
            /* and an extra line for the remaining pallets */

        DECLARE @NumOfLoads int,
        @LoadsRemainder int,
        @CountSplit int,
        @Cnt int

              /* Number of Loads from tblOrders saved to variable */
        @NumOfLoads = NumOfLoads
        @LoadsRemainder = 0
        @CountSplit = 0
        @Cnt = 0

        /* Determine if there are more than 26 loads */
        @LoadsRemainder = @NumOfLoads / 26
        
        if (@LoadsRemainder >= 1) then
                
                while (@LoadsRemainder >= 1)
                        @NumOfLoads = @NumOfLoads - 26
                        @CountSplit = @CountSplit + 1
                        @LoadsRemainder = @NumOfLoads / 26
                end

                for @Cnt = 0 to (@CountSplit - 1)
                        INSERT INTO tblSplitOrders (DeliveryID, CollectionID, TempID, OrderDate, NumOfLoads)
                        SELECT (DeliveryID, CollectionID, TempID, OrderDate, 26) FROM tblOrders
                          end

                INSERT INTO tblSplitOrders (DeliveryID, CollectionID, TempID, OrderDate, NumOfLoads)
                SELECT (DeliveryID, CollectionID, TempID, OrderDate, @NumOfLoads) FROM tblOrders

        else

                INSERT INTO tblSplitOrders (DeliveryID, CollectionID, TempID, OrderDate, NumOfLoads)
                SELECT (DeliveryID, CollectionID, TempID, OrderDate, @NumOfLoads) FROM tblOrders

        end if

END
==============================================================================

The error message I receive:

           Error 170: Line 14: Incorrect syntax near '@NumOfLoads'
           Incorrect syntax near the keyword 'then'
           Line 25: Incorrect syntax near '@NumOfLoads'
           Line 32: Incorrect syntax near ','
           Line 36: Incorrect syntax near ','
           Line 41: Incorrect syntax near ','

I'm not sure if my trigger is the most effective means to achieve my objective and if this trigger
will successfully copy over a single line with up to and including 26 pallets and will copy
lines with a maximum of 26 pallets multiple times until the smallest remainder is copied in as the
last entry with the same DeliveryID, CollectionID, TempID, OrderDate for each entry.

There is a unique incrementing ID in the tblSplitOrders table.

Do you have any suggestions that might assist me.

Thank you kindly
Rhonda

Relevant Pages