Trigger Error - 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)

Trigger Error

Author

Topic

tad
Starting Member

31 Posts

Posted - 2002-06-06 : 14:35:46

When I execute a query that updates multiple rows, the trigger issues an error. Does anyone see the problem ?

Listed below is the Error, Trigger, and Query.

Server: Msg 512, Level 16, State 1, Procedure TR_KYLAB_LOT_GRADE_01, Line 5
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.

CREATE TRIGGER TR_KYLAB_LOT_GRADE_01
ON KYLAB_LOT_GRADE
FOR UPDATE NOT FOR REPLICATION
AS
Update KYLAB_LOT_GRADE SET
updateUserName = user_name(),
updateDateTime = getdate()
WHERE KYLAB_LOT_GRADEID = (
SELECT INSERTED.KYLAB_LOT_GRADEID FROM INSERTED)

UPDATE KYLAB_LOT_GRADE
SET OLD_ITEM = 'TEST'
WHERE ITEM_TYPE_ID = 'FG'
AND ITEM_NO = '2011312LV0'
AND LOT_NO IN ('1000', '1001')

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-06 : 14:51:52


CREATE TRIGGER TR_KYLAB_LOT_GRADE_01 
ON KYLAB_LOT_GRADE 
FOR UPDATE NOT FOR REPLICATION 
AS 
Update KYLAB_LOT_GRADE SET 
updateUserName = user_name(), 
updateDateTime = getdate()
from kylab_log_grad k
WHERE
   exists (
     select 1
     from inserted
     where k.kylab_lot_gradeid = kylab_lot_gradeid )

UPDATE KYLAB_LOT_GRADE 
SET OLD_ITEM = 'TEST' 
WHERE ITEM_TYPE_ID = 'FG' 
AND ITEM_NO = '2011312LV0' 
AND LOT_NO IN ('1000', '1001')

<O>

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-06 : 14:53:26

quote:

.. updates multiple rows ...

quote:

Update KYLAB_LOT_GRADE SET
updateUserName = user_name(),
updateDateTime = getdate()
WHERE KYLAB_LOT_GRADEID = (
SELECT INSERTED.KYLAB_LOT_GRADEID FROM INSERTED)

inserted will have multiple rows. which (of the multiple) kylab_lot_gradeid columns would it use for the '=' operator? that's why it throws the exception.

setBasedIsTheTruepath
<O>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-06 : 14:54:13

...or...


CREATE TRIGGER TR_KYLAB_LOT_GRADE_01 
ON KYLAB_LOT_GRADE 
FOR UPDATE NOT FOR REPLICATION 
AS 
Update KYLAB_LOT_GRADE SET 
updateUserName = user_name(), 
updateDateTime = getdate()
from kylab_log_grad k
inner join inserted i
on k.kylab_lot_gradeid = i.kylab_lot_gradeid 

UPDATE KYLAB_LOT_GRADE 
SET OLD_ITEM = 'TEST' 
WHERE ITEM_TYPE_ID = 'FG' 
AND ITEM_NO = '2011312LV0' 
AND LOT_NO IN ('1000', '1001')

<O>

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-06 : 14:55:45

sniped!

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources