Unanswered: Error: Subquery returned more than 1 value
can anyone help,
i have an update statement that i run from vb code which is:
Update [P_Wrn] SET printed = 'Yes' Where UserName = 'ICAMPBEL'
this is fine.
however, i have now added a trigger to the table P_Wrn for auditing purposes which is the following:
ALTER TRIGGER [dbo].[Audit_P_WRN_Update]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @Version int
SELECT @Version = (SELECT Version FROM Deleted)
declare @WNumber int
SELECT @WNumber = (SELECT W_Number FROM Inserted)
if @Version > 0
insert into [Audit].[dbo].SIRIS_P_WRN SELECT [W_NUMBER]
update P_WRN set Version = Version + 1 Where W_Number = @WNumber
which in itself, works fine, copies the record into the new table.
what does not work is the actual update statement. when i run from code it is falling over with error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Very standard problem - you have written your trigger on the assumption that only one row will be updated. You need to write a set based solution.
HINT - it involves JOINING to deleted rather than mucking about with variables.
Note that you have corrected the symptom not the problem. Better is to correct the trigger. Triggers are notorious for causing real debugging difficulties. I would recommend you work with us and fix the trigger. If not, at the very least thoroughly document it (in the trigger code, sproc code and project documents) so that future developers are aware.