SQL Trigger Error Code

Question

I am trying to develop an SQL Server trigger but it seems to be throwing off an error for me when I update records that have the same values with the trigger keyword value, but if its just one record that matches that value, it is not throwing off an error.

Error code:

In regards to a trigger that I am developing for SNL, it seems it is producing an error: 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.

Trigger Code:

USE [TestTrigger]
GO
/****** Object:  Trigger [dbo].[TestTrigger]    Script Date: 06/04/2015 08:29:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TestTRigger]
   ON  [dbo].[SN_Contact2]
   FOR INSERT,UPDATE
AS 
BEGIN

        DECLARE @IDSTATUS varchar(254)
        DECLARE @CONTACTID VARCHAR(36)
        DECLARE @CLIENTURL nvarchar(254)
        set @CONTACTID = (select contact_contactid from inserted)
        set @IDSTATUS = (select contact_category from inserted)
        set @CLIENTURL = (select CUST_1004 from inserted)

        IF @IDSTATUS = 'Client'
        BEGIN

                                update sn_contact2 set CUST_ID_Status_Change_Date = getdate()
                where CUST_Main_Contact = 'X' and cust_id_status_change_date is null
                and CUST_1004 = @CLIENTURL
        END


END

Any thoughts?

Answer 1

These are the fault lines.. when more than 1 records are updated/inserted through a single query, you will get this error (as sub query is returning more than 1 value..).

set @CONTACTID = (select contact_contactid from inserted)
set @IDSTATUS = (select contact_category from inserted)
set @CLIENTURL = (select CUST_1004 from inserted)

change your update query from a single row update to something like :

update sn_contact2 set CUST_ID_Status_Change_Date = getdate()
                where CUST_Main_Contact = 'X' and cust_id_status_change_date is null
                and CUST_1004 --= @CLIENTURL
in (
select CUST_1004 from inserted where contact_category = 'Client'
)