December 14, 2007 at 9:50 am
I have the below trigger on our Customer table:
FOR UPDATE
AS
BEGIN
DECLARE @CustomerID int
SET @CustomerID = (SELECT CustomerID FROM INSERTED)
EXEC dbo.CheckCustomerLevel @CustomerID
END
The problem is, we run frequent UPDATE queries where multiple records in the table are updated. Each time I run an UPDATE command w/ the trigger enabled, I get the below error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery.
Another problem is, I think the trigger is too vague. I really only want to call the SP if a specific field is updated. Is there a way to specify this in the trigger?
Thanks in advance for your help.
Stephen
December 14, 2007 at 1:00 pm
This is the problem line (or at least the main problem)
Stephen Lee (12/14/2007)
SET @CustomerID = (SELECT CustomerID FROM INSERTED)
If that select returns more than one row (which will happen if multiple rows are updated in one statement) then it will throw the error you noticed
Fixing the trigger is a little more complex, due to the stored proc call. Since I don't know what it does, is hard to suggest alternatives.
Is that proc called from anywhere other than the trigger?
As for only running when a certain row is changed, look up UPDATE() and COLUMNS_UPDATED() in Books Online
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2007 at 1:07 pm
Thanks for your reply.
That SP is only called from the trigger. The SP checks the order database to see if the customer purchased a membership. The Customer record is updated to 1 level, if not, another level is set.
Thanks.
Stephen
December 16, 2007 at 11:59 am
Than rather move the contents of the stored proc into the trigger, so you don't have to call the proc for each updated row, but can operate on all rows at once.
If you're not sure, post the proc's code here and someone will help out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2007 at 3:31 am
Stephen Lee (12/14/2007)
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery.
Stephen ,before changing your trigger logic,do you know why you got this error message ? Then i can't say the trigger logic is hard.
karthik
December 17, 2007 at 5:29 am
The reason he got the error is, as I mentioned above, due to the subquery only working if there is 1 row in it. It's due to the equality.
The bigger issue is, even without the error, the trigger doesn't, in its current form, handle updates that affect more than one row,
There are two ways to fix this.
1) Cursor through the inerted table and call the stored proc (very bad)
2) Move the proc's logic into the trigger and make it set-based (recommended)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2007 at 5:38 am
It is problem with set. The subquery returing more than on value. While updating in stored procedure add one more conditional check so that your update statement not returns the duplicate values.
December 25, 2007 at 8:33 pm
Try again:
...
FOR UPDATE
AS
DECLARE @CustomerID int
IF @@ROWCOUNT =1
BEGIN
SELECT @CustomerID=CustomerID FROM INSERTED
EXEC dbo.CheckCustomerLevel @CustomerID
END
ELSE
BEGIN
SELECT IDENTITY(INT,1,1) ID,CustomerID INTO #TEMP FROM INSERTED ORDER BY CustomerID
DECLARE TEST_CUR CURSOR FOR SELECT CustomerID FROM #TEMP ORDER BY ID
OPEN TEST_CUR
FETCH NEXT FROM TEST_CUR INTO @CustomerID
WHILE @@FETCH_STATUS=0
BEGIN
EXEC dbo.CheckCustomerLevel @CustomerID
FETCH NEXT FROM TEST_CUR INTO @CustomerID
END
DROP TABLE #TEMP
CLOSE TEST_CUR
DEALLOCATE TEST_CUR
END
December 25, 2007 at 11:42 pm
hxd001_810 (12/25/2007)
Try again:SELECT IDENTITY(INT,1,1) ID,CustomerID INTO #TEMP FROM INSERTED ORDER BY CustomerID
DECLARE TEST_CUR CURSOR FOR SELECT CustomerID FROM #TEMP ORDER BY ID
I would suggest that you get away from the #TEMP table and the CURSOR. My suggestion: [if more than one record is updated]
DECLARE @t TABLE (RowID BIGINT IDENTITY(1,1), CustomerID BIGINT)
INSERT INTO @t (CustomerID) SELECT CustomerID FROM INSERTED
DECLARE @cnt INT, @tot INT
SELECT @cnt = 1, @tot = COUNT(*) FROM @t
DECLARE @PatientID BIGINT
WHILE @cnt <= @tot BEGIN
SELECT @PatientID = PatientID FROM @t WHERE RowID = @cnt
EXECUTE THE STORED PROCEDURE HERE
SET @cnt = @cnt + 1
END
note: does anyone know how to preserve formatting while posting at this forum?
.
December 26, 2007 at 10:53 pm
I'd prefer to rewrite the procedure so that it can handle a set-based solution. Since the proc's only ever called from the trigger, that shouldn't be hard, if the OP would post the code.
While loops are better than cursors, but not by very much.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply