how to handle multi-row results in a 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

SQL Server Development (2000)

how to handle multi-row results in a trigger

Author

Topic

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-10-17 : 05:04:07

Hello,
Here is my problem:

The first part is a SP thats is operator triggerd.
Because of the update of the table INFO_SSCC there is a trigger
that gets fired RECORDSSCC. Because the update can modify more than 1 record
the trigger selected insert holds more than 1 record and returns an error, so the the SP
is also stopped and the update is never executed.

Now I have two questions:

1 - why is the trigger executed when it should only run when the column SHIPMENTNR is updated?

2 - how do you handle multirow resulsets in a trigger?

All tips are greatly appriciated..

/*** This is the script ***/

declare @LOCKED_NR as int

set @LOCKED_NR = 521

/*Used to update a series of SSCC when locked a whole serie*/
UPDATE A
SET A.SSCC_STATUS = B.LOCKED_CODE, A.LOCKED_NR = B.LOCKED_NR
FROM INFO_SSCC A, INFO_LOCKED B
WHERE B.LOCKED_NR = @LOCKED_NR
AND (A.SSCC_STATUS IN (1, 5, 6))
AND B.LIJN_NR = A.LINE_ID
AND( (A.PRODUCTION_DATE >= B.START_DATE AND A.PRODUCTION_DATE <= B.END_DATE )
OR (A.PROD_DATE_LAST >= B.START_DATE AND A.PROD_DATE_LAST <= B.END_DATE ) )
-- Just to see howmany rows where updated
SELECT @@ROWCOUNT AS RETURNVAL

/*** end of script ***/

/*** return messages ***/

Server: Msg 512, Level 16, State 1, Procedure RecordSSCC, Line 16
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

/*** end of return messages ***/

Now the procedure RecordSSCC is a trigger on the table info_SSCC and looks like this. Seems it gets fired, even if it 's not an update of the column shipmentnr.

/*** Begin of trigger ***/

ALTER TRIGGER [RecordSSCC] ON dbo.INFO_SSCC
FOR UPDATE
AS

Begin
IF UPDATE(SHIPMENTNR)
Declare @BESTEMMING as varchar(4)
IF (SELECT SHIPMENTNR FROM INSERTED) > 100
AND (SELECT SHIPMENTNR FROM DELETED) <> (SELECT SHIPMENTNR FROM INSERTED)
AND ( SELECT LEN(BESTEMMINGSCODE) FROM INSERTED) <> 0
Begin
SELECT @BESTEMMING = BESTEMMING FROM INFO_SHIPMENT WHERE CODE =
(SELECT SHIPMENTNR FROM INSERTED)
INSERT INTO IF_RECORDSSCC(SHP, BESTEMMING, SSCCLOG)
SELECT SHIPMENTNR, @BESTEMMING, SSCC FROM INSERTED
--Added update of pallet tracking data
UPDATE INFO_PALLET_TRACKING
SET DATE_SHIPPING = Getdate()--< this is line 16??
WHERE SSCC = (SELECT SSCC FROM INSERTED)
end
Else
IF (SELECT SHIPMENTNR FROM DELETED) > 100
AND (SELECT SHIPMENTNR FROM INSERTED) < 100
AND (SELECT SHIPPED FROM INSERTED) <> 1
Begin
Declare @SSCC as varchar(20)
SELECT @sscc = SSCC FROM INSERTED
SELECT @BESTEMMING = BESTEMMING FROM INFO_SHIPMENT WHERE CODE =
(SELECT SHIPMENTNR FROM DELETED)
INSERT INTO IF_RECORDSSCC(SHP, BESTEMMING, SSCCLOG)
VALUES (0, @BESTEMMING, @SSCC)
end
End

/*** end of trigger ***/

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-17 : 07:04:25

You think that if there is only one row in a table then you can write:

WHERE (SELECT ... FROM ...) = 5

No! You can't. But you must write instead:

WHERE (SELECT TOP 1 ... FROM ...) = 5
or
WHERE ANY(SELECT ... FROM ...) = 5
or
WHERE SOME(SELECT ... FROM ...) = 5

Revise your code in this direction.

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-10-17 : 07:51:19

Thanks for you're response, BUT

1-The first script runs first, does the update of 5 records.
2-the trigger fires on the update of the first record or after all 5 are done?

THX

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-17 : 08:28:51

after all 5 are done

dsdeming

479 Posts

Posted - 2003-10-17 : 08:59:45

I think the problem is here:

SELECT @BESTEMMING = BESTEMMING FROM INFO_SHIPMENT WHERE CODE =
(SELECT SHIPMENTNR FROM DELETED)

The subquery SELECT SHIPMENTNR FROM DELETED is returning more than one row and it follows an equal sign. Don't let the Line 16 part throw you. The line numbers in SQL Server error messages are often inaccurate and sometimes completely useless.

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources