I have a SQL statement that updates a lot of records and a Trigger that fires for updates on the table. The Trigger works fin e when only one record is updated vut generates a Subquery returned more than 1 value error when updating more than one record. What can I do?
The Trigger errors out on the Set @intUid = (Select intUid from Inserted) as shown by the output from tne error text.
CREATE TRIGGER UPDATE_tblUser
FOR UPDATE AS
@intUid as int
Set @intUid = (Select intUid from Inserted)
Server: Msg 512, Level 16, State 1, Procedure UPDATE_tblUser, Line 6
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.
Yep, that it will if more than one row is affected. When a trigger executes, it does so once per triggering statement, not once per triggering row. If the statement that launched the trigger affected 1000 rows, then there would be 1000 rows in the inserted view when the trigger executes.
Do you want to post the rest of the trigger so we can try to help you fix it, or is the score now one question down, N to go?
I have discovered other Triggers with simular problem but the Triggers ard different.
CREATE TRIGGER DELETE_Skids
FOR DELETE AS
@CompanyID as varchar(20),
@SkidID as int
SET @CompanyID=(Select CompIDfrom deleted)
SET @SkidID = (Select intSkidID from deleted)
IF @CompanyID= 'NewCompany'
delete [dbSkids].[dbo].Skids where RolID = @SkidID
This is for deletes and needs the same but differtent solution. I have been trying differnet things after the where .
I have trigger with a different configuration. This one starts out with
set @pwd = (select vchrPwd from inserted) as usual
which of course creats Subquery returned more than 1 value.
I changed every occurance of @pwd to
SELECT vchrPwd from inserted as i join tblUser h on i.intUid = h.intUid
which I was hoping would work but it doesn't. Any ideas?
It actually does work in a perverse sort of way, but it would return multiple values where a scalar was required, which would cause SQL Server to object rather strongly!
The problem with all of your triggers that you've posted so far is that they were written with the idea of processing only one row at a time. In other words, they make the assumption that the inserted and/or deleted tables will have only one row in them. This is obviously a bad assumption based on the problems that you are having.
The code seems to be realatively well written, but with the wrong mind-set (thinking in rows instead of in sets). You probably need to revisit every one of your triggers, and think about the effects of the code when more than one row was affected in the underlying table (meaning that there may be many rows in inserted and deleted). You probably also need to think about the kind of chaos that can be caused by changes to the primary key, which would cause rows in inserted to fail to match rows in deleted!
I don't know of a "one size fits all" solution for your problem. The only way that I know to fix it is to manually review all of the trigger code.