Getting really desperate here - SQL Server Forums

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)

Getting really desperate here

Author

Topic

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-10-21 : 04:20:25

I have a stored proc that does an update of the table Info_SSCC.
On that table there some triggers that get fired, and cause the problem that the first stored proc gets rolled back.
Now I don't know how to solve this.....
So if any of you guy's can help....

/*** error message ***/

Server: Msg 512, Level 16, State 1, Procedure ChangeSSCCStatus, Line 7
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 error message ***/

/*** stored proc ***/
ALTER PROCEDURE [SP_UpdateSSCCStatus]

@LOCKED_NR as int

AS

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

SELECT @@ROWCOUNT AS RETURNVAL

/*** end of stored proc ***/

/*** begin of triggers ***/
ALTER TRIGGER [RecordSSCC] ON dbo.INFO_SSCC
AFTER UPDATE
AS

Begin

IF UPDATE(SHIPMENTNR)
BEGIN
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()
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

/******/

ALTER TRIGGER [ChangeSSCCStatus] ON dbo.INFO_SSCC
AFTER UPDATE
AS
Begin
IF UPDATE(SSCC_STATUS)
BEGIN
IF (SELECT SSCC_STATUS FROM DELETED) <> (SELECT SSCC_STATUS FROM INSERTED)
/*AND (SELECT SSCC_STATUS FROM INSERTED) > 1*/
Begin

INSERT INTO IF_CHANGESSCCSTATUS (SSCCLOG, OUDE_SSCCSTAT, NIEUWE_SSCCSTAT)
SELECT INSERTED.SSCC, DELETED.SSCC_STATUS, INSERTED.SSCC_STATUS
FROM DELETED CROSS JOIN
INSERTED
WHERE (DELETED.SSCC_STATUS <> 9)

IF (SELECT SSCC_STATUS FROM INSERTED) = 9 -- Label destroyed
OR (SELECT SSCC_STATUS FROM INSERTED) = 10 -- Product destroyed
OR (SELECT SSCC_STATUS FROM INSERTED) = 12 -- Personal distribution
OR (SELECT SSCC_STATUS FROM INSERTED) = 13 -- Sponsering
Begin
DECLARE @Shipment as integer, @WeightPallet as decimal(10,4)
SELECT @Shipment = SHIPMENTNR, @WeightPallet = TOT_WEIGHT_PALLET FROM INFO_SSCC WHERE SSCC = (SELECT SSCC FROM INSERTED)

UPDATE INFO_SSCC
SET SHIPMENTNR = 99
WHERE SSCC = (SELECT SSCC FROM INSERTED)

UPDATE INFO_SHIPMENT
SET AANTALPALLETS = AANTALPALLETS - 1, GEWICHTPALLETS = GEWICHTPALLETS - @WeightPallet
WHERE CODE = @Shipment
End
End
END
End

/*** end of triggers ***/

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-10-21 : 06:04:59

It's 5am here in the states, but I think that your problem is this:

SELECT SHIPMENTNR FROM INSERTED

That could return more than one record, and thus give you that error.
Depending on what exactly you need to get done, you'll need a SELECT TOP 1 in those subqueries, or you'll need to re-work it in such a way as to handle N number of records in the inserted table.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-10-21 : 11:04:05

Hello Michael

That's my problem. I want the trigger to able to handle one record, or if needed more than one record.
So how do I handle that. Could you help here?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 12:35:30

dyckwal, add a WHERE clause to that SELECT statement so that you only get one record back. The way that your code is written right now, you'd have to loop through the inserted table until all rows were processed. The reason being is code like this:

SELECT @sscc = SSCC FROM INSERTED

The above will put SSCC from the last row in INSERTED into @sscc. Variables can not hold more than one value.

Tara

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-10-22 : 05:26:14

Hello guy's,

doesn't there excists something like

CREATE OR REPLACE TRIGGER "APPL_WTP".TRG_CHANGESSCCSTATUS BEFORE INSERT ON IF_CHANGESSCCSTATUS FOR EACH ROW

This comes from an Oracle trigger

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-22 : 07:03:21

dyckwal --

stop everything. IN english, not SQL, explain to us what this trigger should be doing and what tables it affects.

for example

"When rows are inserted into table XYZ, if the column ABC = 1, I need to ... ; if the column ABC = 2, i need to ...., etc"

be very detailed but just explain what your trigger needs to accomplish. Forget about oracle syntax or SQL Server syntax, just explain what you need to do.

- Jeff

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-10-23 : 07:54:50

Ok Jeff, btw my name is Walter.

First the procedure.
This is used when we have bad product and we want to mark the pallets containing the bad product as blocked.
So in the table info_locked there is a record created with the reason and begin/end date/time of the period where the bad product has been made. Next the proc, goes and looks in the table INFO_SSCC if there already pallets made with the bad product. If so, the records gets updated, modifying the status of the pallet to 3 and number of the lock record to a followup number coming from info_locked. So they can trace the bad pallets to the reason.

The trigger changessccstatus.
first part:Whenever the status of a pallet changes (from produced to blocked) we insert a record in the IF_CHANGESTATUS. If the new status == 9 (destroyed) we do nothing with the first part.

second part:When the status changes to 9,10,12,13 we have to update the shipment where the pallet was in.
So we fetch the shipmentnr and weight of the pallet, update info_SSCC to set the shipmentnr to 99, and then update the INFO_SHIPMENT with the corrected weigth and number of pallets.

The trigger recordsscc. This should only run when the column SHIPMENTNR is altert.
the first part:We need a trace on al movements a pallet makes. So when we move pallet A from the floor to the truck, then a record is inserted in the table IF_RECORDSSCC.

Second part is the update of the palletrecord when the pallet is been shipped. We update INFO_PALLET_TRACKING to log how it took to make the pallet.
The third part (after the else statement) update the same tables as part one, but only when a pallet comes back from a truck to the floor.

When shipmentnr < 100 means the floor, > 100 means shipment or truck
Initial status of an pallet is 1 (means produced). 3 means pallet is blocked. 9 or 10 means pallet destroyed.12 or 13 means pallet is distributed internal to personal.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources