UPDATE after INSERT for potentially multiple rows - not working

Question

I have the following trigger which doesn't work and I'm not sure why. The trigger should fire after an insert into the REFERRALS table and I've allowed for the possibility of multiple rows being inserted. The value of ORIGINAL_PATIENT_ID in the REFERRALS table should be set to the value of PATIENT_ID in Inserted, but it just doesn't work, i.e. the value of ORIGINAL_PATIENT_ID remains NULL.

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[updateOSC]'))
DROP TRIGGER [dbo].[updateOSC]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[updateOSC]
ON [dbo].[REFERRALS]
AFTER INSERT

AS

BEGIN

SET NOCOUNT ON
IF (SELECT ORIGINAL_PATIENT_ID FROM Inserted) IS NULL
UPDATE [dbo].[REFERRALS] 
SET  ORIGINAL_PATIENT_ID = i.PATIENT_ID 
FROM Inserted i
WHERE dbo.REFERRALS.PATIENT_ID = i.PATIENT_ID
END
GO

Answer 1

If you can have multiple rows being updated then IF (SELECT ORIGINAL_PATIENT_ID FROM Inserted) IS NULL doesn't make much sense to me as this will return multiple values which you cannot compare to NULL.

I think you will get your desired result by using

CREATE TRIGGER [dbo].[updateOSC]
ON [dbo].[REFERRALS]
AFTER INSERT

AS

BEGIN

SET NOCOUNT ON

UPDATE [dbo].[REFERRALS] 
SET  
    ORIGINAL_PATIENT_ID = i.PATIENT_ID 
FROM 
   Inserted i
WHERE 
   dbo.REFERRALS.PATIENT_ID = i.PATIENT_ID AND i.ORIGINAL_PATIENT_ID IS NULL
END
GO