SQL Server Performance Forum – Threads Archive

trigger causing update errors

Hello, I recently put a trigger on one of my tables (proj). It is working fine, but today I tried to run this statement from query analyzer: update proj set fiscalyear = ‘2004’ and got this error: Server: Msg 512, Level 16, State 1, Procedure DupData, 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. I did some research, and found that it could be my trigger causing the problems. Do you know how I can fix this trigger to work correctly? Here is the trigger code: CREATE TRIGGER [DupData] ON [dbo].[Proj] AFTER UPDATE AS declare @ProjID varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@CustomerID varchar(12),@DivisionID varchar(12),@FiscalYear varchar(4),@Abbreviation varchar(1),@DateTimeLastChanged varchar(20) set @ProjID = (select projid from inserted) set @CustomerID = (select CustomerID from inserted) set @DivisionID = (select DivisionID from inserted) set @FiscalYear = (select FiscalYear from inserted) set @Abbreviation = (select Abbreviation from inserted) set @DateTimeLastChanged = (select DateTimeLastChanged from inserted) IF (UPDATE(CustomerID)) BEGIN IF EXISTS(select * from [Case] where [Case].[CaseID] = @ProjID) BEGIN update [Case] set [Case].[CustomerID] = @CustomerID where [Case].[CaseID] = @ProjID END IF EXISTS(select * from [Job] where [Job].[JobID] = @ProjID) BEGIN update [Job] set [Job].[CustomerID] = @CustomerID where [Job].[JobID] = @ProjID END IF EXISTS(select * from [Lead] where [Lead].[LeadID] = @ProjID) BEGIN update [Lead] set [Lead].[CustomerID] = @CustomerID where [Lead].[LeadID] = @ProjID END IF EXISTS(select * from [Proposal] where [Proposal].[ProposalID] = @ProjID) BEGIN update [Proposal] set [Proposal].[CustomerID] = @CustomerID where [Proposal].[ProposalID] = @ProjID END END IF (UPDATE(DivisionID)) BEGIN IF EXISTS(select * from [Case] where [Case].[CaseID] = @ProjID) BEGIN update [Case] set [Case].[DivisionID] = @DivisionID where [Case].[CaseID] = @ProjID END IF EXISTS(select * from [Job] where [Job].[JobID] = @ProjID) BEGIN update [Job] set [Job].[DivisionID] = @DivisionID where [Job].[JobID] = @ProjID END IF EXISTS(select * from [Lead] where [Lead].[LeadID] = @ProjID) BEGIN update [Lead] set [Lead].[DivisionID] = @DivisionID where [Lead].[LeadID] = @ProjID END IF EXISTS(select * from [Proposal] where [Proposal].[ProposalID] = @ProjID) BEGIN update [Proposal] set [Proposal].[DivisionID] = @DivisionID where [Proposal].[ProposalID] = @ProjID END END IF (UPDATE(FiscalYear)) BEGIN IF EXISTS(select * from [Case] where [Case].[CaseID] = @ProjID) BEGIN update [Case] set [Case].[FiscalYear] = @FiscalYear where [Case].[CaseID] = @ProjID END IF EXISTS(select * from [Job] where [Job].[JobID] = @ProjID) BEGIN update [Job] set [Job].[FiscalYear] = @FiscalYear where [Job].[JobID] = @ProjID END IF EXISTS(select * from [Lead] where [Lead].[LeadID] = @ProjID) BEGIN update [Lead] set [Lead].[FiscalYear] = @FiscalYear where [Lead].[LeadID] = @ProjID END IF EXISTS(select * from [Proposal] where [Proposal].[ProposalID] = @ProjID) BEGIN update [Proposal] set [Proposal].[FiscalYear] = @FiscalYear where [Proposal].[ProposalID] = @ProjID END END IF (UPDATE(Abbreviation)) BEGIN IF EXISTS(select * from [Case] where [Case].[CaseID] = @ProjID) BEGIN update [Case] set [Case].[Abbreviation] = @Abbreviation where [Case].[CaseID] = @ProjID END IF EXISTS(select * from [Job] where [Job].[JobID] = @ProjID) BEGIN update [Job] set [Job].[Abbreviation] = @Abbreviation where [Job].[JobID] = @ProjID END IF EXISTS(select * from [Lead] where [Lead].[LeadID] = @ProjID) BEGIN update [Lead] set [Lead].[Abbreviation] = @Abbreviation where [Lead].[LeadID] = @ProjID END IF EXISTS(select * from [Proposal] where [Proposal].[ProposalID] = @ProjID) BEGIN update [Proposal] set [Proposal].[Abbreviation] = @Abbreviation where [Proposal].[ProposalID] = @ProjID END END IF (UPDATE(DateTimeLastChanged)) BEGIN IF EXISTS(select * from [Case] where [Case].[CaseID] = @ProjID) BEGIN update [Case] set [Case].[DateTimeLastChanged] = @DateTimeLastChanged where [Case].[CaseID] = @ProjID END IF EXISTS(select * from [Job] where [Job].[JobID] = @ProjID) BEGIN update [Job] set [Job].[DateTimeLastChanged] = @DateTimeLastChanged where [Job].[JobID] = @ProjID END IF EXISTS(select * from [Lead] where [Lead].[LeadID] = @ProjID) BEGIN update [Lead] set [Lead].[DateTimeLastChanged] = @DateTimeLastChanged where [Lead].[LeadID] = @ProjID END IF EXISTS(select * from [Proposal] where [Proposal].[ProposalID] = @ProjID) BEGIN update [Proposal] set [Proposal].[DateTimeLastChanged] = @DateTimeLastChanged where [Proposal].[ProposalID] = @ProjID END END Thanks for you help, Ben

You haven’t anticipated updates affecting multiple records. You get this error when there are multiple records in the "inserted" table. You can only SET a variable to a value from the inserted table if not more than 1 record was affected. Since the tables that you need to modify all have a foreign key referring to ProjID, you can simply use a join: UPDATE <othertable> SET <othertable>.<otherfield> = inserted.<field>
FROM <othertable> INNER JOIN inserted on <othertable>.<id> = inserted.projid

Thanks a lot. That solved my problem.

]]>