big problem with this trigger...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

big problem with this trigger...

Author

Topic

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2005-10-21 : 11:42:18

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-21 : 13:23:42

Something along the lines of:

INSERT INFO_EXPORT_MOVEMENT(...)
SELECT ... -- replace the IF statements with CASE
FROM inserted i JOIN deleted d ON i.thePrimaryKey = d.thePrimaryKey -- join on the pk, if the pk can be updated as well you have more work
JOIN INFO_SHIPMENTS_INBOUND isi ON i.ID_SHIPMENT = isi.ID_SHIPMENT

would probably do it.

Edit: the above is for UPDATE trigger,
to include INSERT you might want to separate the logic, or use LEFT join between inserted and deleted tables.

rockmoose

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2005-10-22 : 16:50:26

Hello Rockmoose,

I understand only part of youre solution.
If you have the time, could explain a little bit more, since i am fairly new at this kind of TSQL statements.
(problem is that I have to put this in service next week :-( )

Thank for your time and effort..

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2005-10-22 : 17:53:35

Did an effort. Think it should look like this ..

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-22 : 18:30:05

The preferred way when coding triggers is to join the inserted and deleted tables.
The reason is that the trigger will work for multirow inserts/deletes/updates.
in your case it might look something like this:

INSERT INTO INFO_EXPORT_MOVEMENT(
	ItemCode,
	Quantity,
	MovementType,
	LocationFrom,
	LocationTo,
	RefEbly,
	RefSupplier)
SELECT
	i.ITEM_CODE,
	i.UNITS * i.SUB_UNITS,
	MovementType = CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END,
	PrevLocation = CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END,
	NewLocation = CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END,
	isi.ORDER_NR AS RefEbly,
	isi.TRUCK_NR AS RefSupplier
FROM
	INSERTED i
	JOIN DELETED d ON i.thePrimaryKey(s) = d.thePrimaryKey(s)
	JOIN INFO_SHIPMENTS_INBOUND isi ON i.ID_SHIPMENT = isi.ID_SHIPMENT

Another solution is like you suggested to work with cursors, or maybe even temporary tables.
But that is usually a sign of less mature sql coding skills,
or too complex business logic in the trigger/database that can't be solved properly in a set-based manner.

I'm not so sure what your trigger is supposed to do on insert,
@PrevStatus and @PrevLocation will be NULL.

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-22 : 18:35:11

Oooops, Sorry !
I did not see that you had posted another reply.

That was exactly what I was talking about !!!

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 00:50:43

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

Are you only creating a MOVEMENT row on an UPDATE to INFO_SHIPMENTS_INBOUND? Not on the original INSERT into INFO_SHIPMENTS_INBOUND?

If so fine, if not then this needs a bit more work!

Kristen

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2005-10-23 : 07:22:46

Rockmoose, Kristen

You are right, My join is wrong, Rockmoose is right.
It has to be a join between insert and deleted on a key field called SSCC. And a join between inserted and info_shipment_Inbound.
So need to ajusted that part..

Thanks for you're advise , I will keep you informed on further evolution...

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 08:07:17

I don't think there is any difference between your Join and Rocky's.

So if you DO need to insert a row in INFO_SHIPMENTS_INBOUND on the INSERT action of trigger (rather than just the UPDATE action) then I can advise on what you need to change.

Kristen

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2005-10-23 : 09:54:17

Kristen,

I should do an insert into the table Export_Movements
Whenever there is an insert or update on the table Info_SSCC_inbound.
So the trigger would be on info_sscc_inbound. The Info_Shipment I only need for the field RefEbly, RefSupplier.
The join between the insert and delete on Info_SSCC_inbound should be on field called SSCC - the unique indentifier for each record (which is a pallet). And for each pallet there is a link to info_shipment_Inbound, which is the Shipment_Nr.
Hope this is clear ?
Thanks again for you're help.

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2005-10-23 : 10:04:23

So I think it should look like this :

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,
isi.ORDER_NR,
isi.TRUCK_NR
FROM DELETED AS d INNER JOIN
INSERTED AS i ON d.SSCC = i.SSCC INNER JOIN
INFO_SHIPMENTS_INBOUND isi ON i.ID_SHIPMENT =
isi.ID_SHIPMENT

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 10:37:53

I forgot to ask, is there any insert into INFO_SHIPMENTS_INBOUND on a DELETE action? (Your trigger isn't set up to handle DELETEs, but I just thought I'd check.)

For a trigger that takes care of INSERT and UPDATE you need:


FROM	inserted AS i 
	JOIN INFO_SHIPMENTS_INBOUND isi 
		ON isi.ID_SHIPMENT = i.ID_SHIPMENT
	LEFT OUTER JOIN deleted AS d 
		ON d.SSCC = i.SSCC

for an INSERT only action then d.SSCC will be NULL - so you can test for that if any of your CASE statements need to do something special - for an UPDATE (provided that the PK field SSCC has not changed

- then there will be values in both the "inserted" and "deleted" tables.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-23 : 10:43:03

The INNER JOIN between inserted and deleted tables work for UPDATE trigger.
In the case of an INSERT, there is no deleted table,
so you might want to use:
INSERTED AS i LEFT JOIN DELETED AS d ON d.SSCC = i.SSCC

However all the d.ID_LOCATION, d.* would be NULL in the case of an INSERT.

Beware of updated primary key values (SSCC), you can't associate the inserted + deleted tables on a value that is updated.
That might not be a problem, I just wanted to make you aware of it.

Edit:

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 10:48:57

http://www.google.com/search?q=type+faster

Kristen

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2005-10-23 : 11:48:34

Sorry guys,
Now I'm getting confused.
The table INFO_SSCC_INBOUND should have a trigger on insert and updated. The sscc does not get updated, only the fields which are being used in the case statement (id_location and Status)
Which joins do i need to use ? (getting lost with the left, right...sorry)

So at the insert the deleted values will be zero (or null)
at the insert only the values are updated and both the deleted and the insert hold values.
So should I go kristen solution
FROM inserted AS i
JOIN INFO_SHIPMENTS_INBOUND isi
ON isi.ID_SHIPMENT = i.ID_SHIPMENT
LEFT OUTER JOIN deleted AS d
ON d.SSCC = i.SSCC

or rockmoose
INSERTED AS i LEFT JOIN DELETED AS d ON d.SSCC = i.SSCC

And again , thanks for your time and efforts...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-23 : 12:09:04

quote:
Originally posted by Kristen

http://www.google.com/search?q=type+faster

Yeah,
I'm thinking of getting one of these: http://www.daskeyboard.com
that should speed up the learning process, break out of my old habits of sticking nose into keyboard instead keeping the eyes on the screen.

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 12:17:22

Rocky's is better.

Including INFO_SHIPMENTS_INBOUND, as I did, is a waste of time - it will already have all the data from the INSERT or UPDATE in it - so "inserted" has the same data.

Unless someone can tell me otherwise there is nothing you can get from INFO_SHIPMENTS_INBOUND that is not also in "inserted".

But either should work the same!

Just in case it clarifies:

Your SSCC column will not change - therefore you can safely JOIN inserted and deleted.

You will LEFT JOIN deleted - so you will always get data in inserted, and for an UPDATE there will also be data in deleted (i.e. the data from the original record), for an INSERT all columns in deleted will be NULL.

Kristen

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 12:18:57

"I'm thinking of getting one of these"

A NULL keyboard - how cool is that for a DBA geek! Gotta have one!

Now then - where's the ¬ key gone?!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources