=> Trigger to split Trailer Loads

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

Date: Thu, 8 Apr 2004 08:41:03 -0700

Hello,

I am trying to design a trigger that upon record INSERT checks the number of pallets ordered
and ensures that no record has more than 26 pallets, as this is the maximum that
will fit onto a trailer. Some how dividing 26 into the number of pallets copying all lines with less
than 26 pallets into a new table and copying records with over 26 pallets in quantities of 26 and
then the final last record of less than 26.

Before Trigger:
tblOrder

       DeliveryID CollectionID TempID OrderDate NumOfLoads
            2 3 3 30/03/2004 45
            2 4 3 30/03/2004 27
            2 5 3 30/03/2004 13

After Trigger: copied split entries of max 26 pallets to a new table
tblSplitOrder

        Id DeliveryID CollectionID TempID OrderDate NumOfLoads
        1 2 3 3 30/03/2004 26
        2 2 3 3 30/03/2004 19
        3 2 4 3 30/03/2004 26
        4 2 4 3 30/03/2004 1
        5 2 5 3 30/03/2004 13

I tried the follow trigger but have not got very far - struggling. Not sure how to code trigger to achieve end.
I'm not sure I explained my problem effectively in my last post. Hope you can help.

Thank you very much for any ideas you might have.

Best Regards
Rhonda

=============================================================================
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
==============================================================================

Relevant Pages