Re: => Query to copy tbl1 split entries into tbl2

From: MGFoster (me_at_privacy.com)
Date: 04/08/04 

Date: Thu, 08 Apr 2004 18:32:33 GMT

Rhonda Fischer wrote:

> Hello,
>
> I am trying to design a query 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 Loads
> 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 Loads
> 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 query but have not got very far -
> struggling. Hope you can help.
>
> Thank you very much for any ideas you might have.
>
> Best Regards
> Rhonda
>
>
> I tried to use the follow as a framework but it doesn't
> work in it's current form and I haven't got very far
> redesigning it.
> ===========================================================
> ==================
> 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

Instead of using a Trigger, it may be better to create a stored
procedure (SP) to accomplish the same thing.

 From reading your trigger definition it appears that you need to read &
understand the Books on Line (BOL) article on CREATE TRIGGER (I
recommend CREATE PROCEDURE also).

E.g.: This trigger should only be on INSERT, UPDATE and not on DELETE.
  The DELETE trigger should be a separate trigger, since it would be
deleting data not appending (INSERT INTO).

An UPDATE trigger would need to use "inserted" and "deleted" table
references. E.g., this:

   @NumOfLoads = NumOfLoads

would be this:

   @NumOfLoads = inserted.NumOfLoads

There is no FOR...NEXT control structure in T-SQL; you'd use a WHILE
loop instead, or possible a CURSOR & a WHILE loop.

The SQL Books On Line can be obtained here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en 

-- 
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Relevant Pages