Big Problem with this trigger
-
hello everyone
I have this trigger on a table, that works just fine when 1 record is updated.
But when 2 or more are update, which is mostly the case (off course).
Maybe I should use a cursor for this....
Can someone help to set this right...
this is the code of the trigger now (I know it looks weird but he, i'm not so big on TSQL
)ALTER TRIGGER [InsertExportMovement] ON dbo.INFO_SSCC_INBOUND
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
BEGIN
DECLARE @PrevStatus as tinyint,
@PrevLocation as tinyint,
@NewStatus as tinyint,
@NewLocation as tinyint,
@sPrevLocation as tinyint,
@sNewLocation as varchar(2),
@RefEbly as varchar(15),
@RefSupplier as varchar(15),
@MovementType as integer
-- Init local var:
SET @PrevStatus = 0
SET @PrevLocation = 0
SET @NewStatus = 0
SET @NewLocation = 0
SET @sPrevLocation = 0
SET @sNewLocation = 0
SET @RefEbly = '*'
SET @RefSupplier = '*'
SET @MovementType = 0
-- Get the right info out
SELECT @PrevStatus = STATUS, @PrevLocation = ID_LOCATION FROM DELETED
SELECT @NewStatus = STATUS, @NewLocation = ID_LOCATION FROM INSERTED
-- Get the shipment info
SELECT @RefEbly = ORDER_NR, @RefSupplier = TRUCK_NR
FROM INFO_SHIPMENTS_INBOUND
WHERE ID_SHIPMENT = (SELECT ID_SHIPMENT FROM INSERTED)
-- Check some exceptions
IF @NewLocation > 10
SET @NewLocation = 2
--If its a new pallet, it has no PrevLocation
IF @PrevLocation = 0
SET @PrevLocation = 7
--If we update the status, then we have to set some location..
IF @NewStatus = 9 --Destroy
SET @NewLocation = 4
--IF @NewStatus = 9 AND @PrevLocation = 2 --Destroy
-- SET @NewLocation = 4
IF @PrevStatus = 2 AND @NewStatus = 9
SET @PrevLocation = 3
IF @NewStatus = 2 --Blocked
SET @NewLocation = 3
IF @NewStatus = 1 AND @PrevLocation = 2 --Free
SET @PrevLocation = 3
--Get the info right for the movementtype
IF @PrevLocation = 7 AND @NewLocation = 2
SET @MovementType = 1
IF @PrevLocation = 2 AND @NewLocation = 7
SET @MovementType = 2
IF @PrevLocation = 2 AND @NewLocation IN (5,6)
SET @MovementType = 3
IF @PrevLocation IN (5,6) AND @NewLocation = 2
SET @MovementType = 4
IF @PrevLocation = 2 AND @NewLocation = 3
SET @MovementType = 5
IF @PrevLocation = 3 AND @NewLocation = 2
SET @MovementType = 6
IF @PrevLocation = 2 AND @NewLocation = 4
SET @MovementType = 7
IF @PrevLocation = 3 AND @NewLocation = 4
SET @MovementType = 8
-- All OK, Do the insert
INSERT INTO INFO_EXPORT_MOVEMENT(
ItemCode,
Quantity,
MovementType,
LocationFrom,
LocationTo,
RefEbly,
RefSupplier)
SELECT
ITEM_CODE,
UNITS * SUB_UNITS,
@MovementType,
@PrevLocation,
@NewLocation,
@RefEbly,
@RefSupplier
FROM INSERTED
END
-
A cursor will be horrible for performance and is not required.
You need to make your final INSERT set-based, and include all the derivations in the SELECT. Don't have time to go through all of them, but here is an example of getting rid of the @NewLocation variable, and using an inline CASE...END in the select to replicate the logic, so that it works on multiple records.
For the data you're pulling from an additional table, you join to it so that again, it works on a recordset.
INSERT INTO INFO_EXPORT_MOVEMENT(
ItemCode,
Quantity,
MovementType,
LocationFrom,
LocationTo,
RefEbly,
RefSupplier)
SELECT
ITEM_CODE,
UNITS * SUB_UNITS,
@MovementType,
@PrevLocation,
CASE
WHEN i.ID_LOCATION > 10 THEN 2
ELSE i.ID_LOCATION
END,
s.ORDER_NR,
s.TRUCK_NR
FROM
INSERTED As i
INNER JOIN
INFO_SHIPMENTS_INBOUND As s
ON s.ID_SHIPMENT = i.ID_SHIPMENT
-
Hello adict,
Part of your example I understand, about the case statement for the'new value', But how do I get the 'Delete' or Previous value into the picture?
I have no idea. Could you please if you have the time give me an example on that..
Thanks a lot for your help (need to get this up and running next week
) -
Ok gave it a try. Think it should look like this....
Wat do you think.. Thanks
INSERT INTO INFO_EXPORT_MOVEMENT(
ItemCode,
Quantity,
MovementType,
LocationFrom,
LocationTo,
RefEbly,
RefSupplier)
SELECT
ITEM_CODE,
UNITS * SUB_UNITS,
CASE
WHEN d.ID_LOCATION = 7 AND i.ID_LOCATION = 2 THEN 1
WHEN d.ID_LOCATION = 2 AND i.ID_LOCATION = 7 THEN 2
WHEN d.ID_LOCATION = 2 AND i.ID_LOCATION IN (5,6) THEN 3
WHEN d.ID_LOCATION in (5,6) AND i.ID_LOCATION = 2 THEN 4
WHEN d.ID_LOCATION = 2 AND i.ID_LOCATION = 3 THEN 5
WHEN d.ID_LOCATION = 3 AND i.ID_LOCATION = 2 THEN 6
WHEN d.ID_LOCATION = 2 AND i.ID_LOCATION = 4 THEN 7
WHEN d.ID_LOCATION = 3 AND i.ID_LOCATION = 4 THEN 8
END,
CASE
WHEN d.ID_LOCATION = 0 THEN 7
WHEN d.STATUS = 2 AND i.STATUS = 9 THEN 3
WHEN i.STATUS = 1 AND d.ID_LOCATION = 2 THEN 3
END,
CASE
WHEN i.ID_LOCATION > 10 THEN 2
WHEN i.STATUS = 9 THEN 4
WHEN i.STATUS =2 THEN 3
END,
s.ORDER_NR,
s.TRUCK_NR
FROM DELETED AS d INNER JOIN
INFO_SHIPMENTS_INBOUND AS s ON d.ID_SHIPMENT = s.ID_SHIPMENT INNER JOIN
INSERTED AS i ON s.ID_SHIPMENT = i.ID_SHIPMENT
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply