July 31, 2003 at 2:12 pm
I have a trigger on a master table that I want to conditionally update a single field of all child rows in the child table. But when it runs, it returns an error...
Subquery returned more than one vaule. This is not permitted when a subquery follows =, !=, <, <=, >, >=, or is used as an expression. The statement has been terminated.
The trigger:
ALTER TRIGGER dbo.rb_CascadePendingFlag
ON dbo.RateBook
FOR UPDATE
AS
DECLARE @p AS BIT
DECLARE @rbID as udt_RateBookID
/*
*Cascade an update of IsPending = 0 down to rate sheets. Ratesheet table
*should only cascade an update of IsPending = 1 up to Ratebook to avoid
*a circular cascade
*/
IF UPDATE(IsPending)
BEGIN
SET @p = (SELECT IsPending FROM INSERTED)
SET @rbID = (SELECT RateBookID FROM INSERTED)
IF @p = 0
BEGIN
UPDATE Ratesheet
SET IsPending = 0
WHERE
FK_RateBookID = @rbID
--FK_RateBookID IN (SELECT RateBookID FROM inserted)
END
END
Using creative commenting, I've determined that the offending line is
SET @rbID = (SELECT RateBookID FROM INSERTED)
Also, when I uncomment the same line in the WHERE clause, it returns the same error. So, my question is, when are there multiple records in inserted for a unique record when it's getting updated?
Note that I get this error when I change the value of the IsPending field for a single record in a grid. I have also tried limiting the SELECT by using TOP 1, to no avail.
Thx.
-------
at us, very deafly, a most stares
collosal hoax of clocks and calendars
eecummings
-------
at us, very deafly, a most stares
collosal hoax of clocks and calendars
eecummings
July 31, 2003 at 2:31 pm
This error happens when more than one record is in the Inserted table. This can easily happen on an Update trigger.
For example, consider the statement:
Update MyTable Set MyField = 1
If you had an Update trigger on MyTable, the inserted table would have one row for each row in MyTable.
To work around this, you need to code for the multi-row situation. Code like this should work:
IF UPDATE(IsPending)
BEGIN
Update Ratesheet
Set IsPending = 0 --(always set to 0?)
From RateSheet r
INNER JOIN inserted i on r.FK_RateBookID = i.RateBookID
INNER JOIN deleted d on i.RateBookID = d.RateBookID
WHERE i.IsPending <> d.isPending -- Only if IsPending changed
END
July 31, 2003 at 3:07 pm
quote:
This error happens when more than one record is in the Inserted table. This can easily happen on an Update trigger.
Oh, I understand this. But this error is returned when I'm changing a single record in a grid, the grid in Enterprise Manager precisely. Logically, the INSERTED table should only contain a single record. Academic, though...I see that I need to code this way.
This 'IsPending' field is only changed to false (0) because the changing records in the child table, marking a document 'Pending', runs a trigger updating the IsPending field in the parent table to true (1).
The trigger here will allow me to mark the master as 'not pending' and have that change cascasde down to the child records.
Question: why do you need
WHERE i.IsPending <> d.isPending -- Only if IsPending changed
if I use
IF UPDATE(IsPending)
?
-------
at us, very deafly, a most stares
collosal hoax of clocks and calendars
eecummings
-------
at us, very deafly, a most stares
collosal hoax of clocks and calendars
eecummings
July 31, 2003 at 9:59 pm
IF UPDATE(IsPending) tell you that for at least one of the row(s) the column IsPending was changed.
The part WHERE i.IsPending <> d.isPending check each row to determine for which of the row(s) did the IsPending actially changed. Other the before value of IsPending is not the same as the after value.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply