jeudi 11 juin 2015

UPDATE after INSERT for potentially multiple rows - not working

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

Aucun commentaire:

Enregistrer un commentaire