Error when running this script

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)

Error when running this script

Author

Topic

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-10-15 : 05:15:56

A week ago I posted a script that was provided by you guys.
I tried it out on a copy of the table and it worked fine. But
now when I run the script it comes back with an error that I don't understand. On my copy table there where no triggers

, on the live one there are triggers.

All tips are welcome.

/*** 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 how 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 ***/

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-15 : 07:23:05

Your trigger is not constructed to handle multi-row dml. For example "IF (SELECT SHIPMENTNR FROM INSERTED) > 100", begs the question, "Which SHIPMENTR are you talking about?", if there are multiple rows in INSERTED.

Jay White
{0}

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-10-15 : 07:40:48

That's what I tought. But how can I solve this. I should think that it only runs through the
code when an update is being done on the column shipmentnr...
< IF UPDATE(SHIPMENTNR) > then continue

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources