=> Query to copy tbl1 split entries into tbl2

[Rhonda Fischer]

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

 

[MGFoster]

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/...b1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en

 

[Rhonda Fischer]

Hello,

Thank you MGFoster for your reference to the SQL Books
On Line, that was very helpful. I am now using a cursor
with a while loop. It is partially working however seems
to have recursive behaviour that means the output is
incorrect.

'****************************************************
INSERT INTO tblOrders (DeliveryID, CollectionID, TempID,
OrderDate, NumOfLoads)
VALUES ( 8, 8, 3, '2004-04-13', 5)
INSERT INTO tblOrders (DeliveryID, CollectionID, TempID,
OrderDate, NumOfLoads)
VALUES ( 9, 8, 3, '2004-04-13', 9)
INSERT INTO tblOrders (DeliveryID, CollectionID, TempID,
OrderDate, NumOfLoads)
VALUES ( 10, 8, 3, '2004-04-13', 18)
'********************************************************

Outputs the following trace statements according to my
print comments in the Trigger:

Load values of first record into local variables
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Close the Cursor

(1 row(s) affected)

Load values of first record into local variables
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Close the Cursor

(1 row(s) affected)

Load values of first record into local variables
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Close the Cursor

(1 row(s) affected)
'*******************************************************
And produces the following entries in the tblSplitOrders
table. One entry whether less than or greater than
the maximum 26 loads is correctly copied into this table
as soon as more than one entry is inserted problems occur?

DeliveryID CollectionID TempID OrderDate Loads
8 8 3 13/04/2004 5
8 8 3 13/04/2004 5
9 8 3 13/04/2004 9
8 8 3 13/04/2004 5
9 8 3 13/04/2004 9
10 8 3 13/04/2004 18
'*******************************************************
This is the trigger that I am using. It successfully
splits loads greater than 26 into multiples of 26 with the
remaining load. It just doesn't handle more than one
insert effectively. It seems to load the first entry in
the cursor twice... Do you have any ideas where I have
gone wrong, your suggestions are most appreciated.

Thank you kindly

Best Regards
Rhonda

'*******************************************************
CREATE TRIGGER trgLoadExceedTrlCapacity ON [tblOrders]
FOR INSERT
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 */

Declare @intDeliveryID int, @intCollectionID int,
@intTempID int, @dteOrderDate datetime,
@intNumOfLoads int, @intLineSplit int,
@intLoadsRemainder int, @intMaxLoads int,
@LoadExcessCursor Cursor

Set @intMaxLoads = 26

Set @LoadExcessCursor = Cursor For
Select DeliveryID, CollectionID, TempID,
OrderDate, NumOfLoads,
cast(round
(NumOfLoads/@intMaxLoads, 0) as int) as LineSplit,
NumOfLoads - (cast(round
(NumOfLoads/@intMaxLoads, 0) as int) * @intMaxLoads) as
LoadsRemainder
From tblOrders

Open @LoadExcessCursor

/*Load values of first record into local variables
*/
print 'Load values of first record into local
variables'
Fetch Next From @LoadExcessCursor
Into @intDeliveryID, @intCollectionID, @intTempID,
@dteOrderDate, @intNumOfLoads, @intLineSplit,
@intLoadsRemainder

While (@@FETCH_STATUS = 0)
begin
print 'Enter Cursor'
if @intLineSplit = 0
begin
print 'Enters LineSplit = 0'
/* NumOfLoads between 0-26 insert
without splitting order lines*/
INSERT INTO tblSplitOrders
(DeliveryID, CollectionID, TempID, OrderDate, NumOfLoads)
VALUES ( @intDeliveryID,
@intCollectionID, @intTempID, @dteOrderDate,
@intNumOfLoads)
end
else
if @intLineSplit > 0
begin
print 'Enters LineSplit >0'
/* NumOfLoads greater than 26 -
Split Loads to have a maximum of 26 Loads per record */
/* loop to Insert split loads */
While @intLineSplit >= 1
begin
print 'Split load by
multiples of 26 - loop and insert here'
INSERT INTO
tblSplitOrders (DeliveryID, CollectionID, TempID,
OrderDate, NumOfLoads)
VALUES ( @intDeliveryID,
@intCollectionID, @intTempID, @dteOrderDate, @intMaxLoads)

Set @intLineSplit =
@intLineSplit - 1
end
/* insert last entry with
NumOfLoads equal to LoadsRemainder */
print 'Insert Loads Remainder'
INSERT INTO tblSplitOrders
(DeliveryID, CollectionID, TempID, OrderDate, NumOfLoads)
VALUES ( @intDeliveryID,
@intCollectionID, @intTempID, @dteOrderDate,
@intLoadsRemainder)
end
print 'Loop to next record in the cursor'
Fetch Next From @LoadExcessCursor
Into @intDeliveryID, @intCollectionID,
@intTempID, @dteOrderDate, @intNumOfLoads, @intLineSplit,
@intLoadsRemainder
end

print 'Close the Cursor'
Close @LoadExcessCursor
Deallocate @LoadExcessCursor