Trigger inserting invalid records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Trigger inserting invalid records

Author

Topic

ckedwards65
Starting Member

4 Posts

Posted - 2013-05-30 : 00:16:56

I have a trigger on a table that is intended to create a row in another table when one of two fields is populated.

Can someone please tell me how the following code is resulting in a record being inserted into the EXPIRE table with a null dateexpired and 0iddeathcode?

Any help would be greatly appreciated.

/****** Object:  Trigger [dbo].[tr_patd_expired]    Script Date: 05/29/2013 22:40:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER TRIGGER [dbo].[tr_patd_expired] ON [dbo].[Patd]  
FOR INSERT, UPDATE  
   AS  
SET NOCOUNT ON  
--IF (new.DeathCodeID IS NOT NULL OR new.dateexpired IS NOT NULL)   
BEGIN 
	
	DECLARE @OldIdTreatmentType INT,
			@NewIdTreatmentType INT,
			@Today DATETIME,
			@IdClinicNumber INT,
			@IdPatient INT,
			@Type VARCHAR(10),  
            @SubType VARCHAR(10),
			@NewModality INT,
			@OldModality INT
	
	SET  @Today = GETDATE()
    IF NOT EXISTS (SELECT 1 FROM Expire JOIN INSERTED ON Expire.IdPatient = INSERTED.IdPatient)   
    BEGIN  
       INSERT INTO Expire (  
             DateExpired  
             ,IdCauseOfDeath  
             ,idClinicNumber  
             ,IdPatient  
            )  
      SELECT  new.ExpiredDate  
             ,new.DeathCodeID  
             ,new.IdClinicNumber  
             ,new.IdPatient  
        FROM INSERTED new  
             LEFT OUTER JOIN DELETED old  
               ON (new.[IdClinicNumber] = old.[IdClinicNumber] and   
                      new.IdPatd = old.IdPatd  
             )  
        WHERE (new.DeathCodeID IS NOT NULL OR new.ExpiredDate IS NOT NULL)  
          AND (ISNULL(old.DeathCodeID,0) != ISNULL(new.DeathCodeID,0)   
               OR ISNULL(old.ExpiredDate,GETDATE()) != ISNULL(new.ExpiredDate,GETDATE()))  
    END  
    else  
    BEGIN  
       UPDATE Expire   
       SET      DateExpired = new.ExpiredDate  
             ,IdCauseOfDeath = new.DeathCodeID  
             ,idClinicNumber = new.idclinicnumber         
    FROM INSERTED new  
            JOIN Expire e   
              ON new.IdPatient = e.IdPatient  
             AND new.idClinicNumber = e.idclinicnumber  
         LEFT OUTER JOIN DELETED old  
           ON (new.[IdClinicNumber] = old.[IdClinicNumber] and   
                  new.IdPatd = old.IdPatd)  
        WHERE (new.DeathCodeID IS NOT NULL OR new.ExpiredDate IS NOT NULL)  
          AND (   ISNULL(old.DeathCodeID,0) != ISNULL(new.DeathCodeID,0)   
               OR ISNULL(old.ExpiredDate,GETDATE()) != ISNULL(new.ExpiredDate,GETDATE()))  
    END 
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:17:16

thats because of ISNULL conditions on WHERE. So when you insert a value for dateexpired or deathcode which was already NULL the where condition will be tru and hence it will do the insert/update based on whether the patient record already existed or not. Similar is case when someone tries to change existing values of dateexpired or deathcode in Patd table to NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ckedwards65
Starting Member

4 Posts

Posted - 2013-05-30 : 10:32:55

quote:
Originally posted by visakh16

thats because of ISNULL conditions on WHERE. So when you insert a value for dateexpired or deathcode which was already NULL the where condition will be tru and hence it will do the insert/update based on whether the patient record already existed or not. Similar is case when someone tries to change existing values of dateexpired or deathcode in Patd table to NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks for your response. Here is the situation:

The PATD record already exists and the dateexpired = null and the DeathCodeID = 0. Someone updates a field in the PATD record other than the dateexpired or DeathCodeID fields. So in the PATD table the dateexpired = null and the DeathCodeID = 0. For some reason, this trigger is inserting a record into the EXPIRE table and the values in the EXPIRE table are ExpiredDate = null and Deathcodeid = 0. I thought the where clause should stop this from happening.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-31 : 02:19:11

it wont in the current way. If want to prevent it you need to add a check if columns were involved in update like below



/****** Object:  Trigger [dbo].[tr_patd_expired]    Script Date: 05/29/2013 22:40:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER TRIGGER [dbo].[tr_patd_expired] ON [dbo].[Patd]  
FOR INSERT, UPDATE  
   AS  
SET NOCOUNT ON  
--IF (new.DeathCodeID IS NOT NULL OR new.dateexpired IS NOT NULL)   
BEGIN 
	
	DECLARE @OldIdTreatmentType INT,
			@NewIdTreatmentType INT,
			@Today DATETIME,
			@IdClinicNumber INT,
			@IdPatient INT,
			@Type VARCHAR(10),  
            @SubType VARCHAR(10),
			@NewModality INT,
			@OldModality INT
	
	SET  @Today = GETDATE()
IF UPDATE(ExpiredDate) OR UPDATE(Deathcodeid)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Expire JOIN INSERTED ON Expire.IdPatient = INSERTED.IdPatient)   
    BEGIN  
       INSERT INTO Expire (  
             DateExpired  
             ,IdCauseOfDeath  
             ,idClinicNumber  
             ,IdPatient  
            )  
      SELECT  new.ExpiredDate  
             ,new.DeathCodeID  
             ,new.IdClinicNumber  
             ,new.IdPatient  
        FROM INSERTED new  
             LEFT OUTER JOIN DELETED old  
               ON (new.[IdClinicNumber] = old.[IdClinicNumber] and   
                      new.IdPatd = old.IdPatd  
             )  
        WHERE (new.DeathCodeID IS NOT NULL OR new.ExpiredDate IS NOT NULL)  
          AND (ISNULL(old.DeathCodeID,0) != ISNULL(new.DeathCodeID,0)   
               OR ISNULL(old.ExpiredDate,GETDATE()) != ISNULL(new.ExpiredDate,GETDATE()))  
    END  
    else  
    BEGIN  
       UPDATE Expire   
       SET      DateExpired = new.ExpiredDate  
             ,IdCauseOfDeath = new.DeathCodeID  
             ,idClinicNumber = new.idclinicnumber         
    FROM INSERTED new  
            JOIN Expire e   
              ON new.IdPatient = e.IdPatient  
             AND new.idClinicNumber = e.idclinicnumber  
         LEFT OUTER JOIN DELETED old  
           ON (new.[IdClinicNumber] = old.[IdClinicNumber] and   
                  new.IdPatd = old.IdPatd)  
        WHERE (new.DeathCodeID IS NOT NULL OR new.ExpiredDate IS NOT NULL)  
          AND (   ISNULL(old.DeathCodeID,0) != ISNULL(new.DeathCodeID,0)   
               OR ISNULL(old.ExpiredDate,GETDATE()) != ISNULL(new.ExpiredDate,GETDATE()))  
    END
END 
END

One question though, why have you created this trigger for INSERT action as well?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-02 : 18:32:44

Or use MERGE?

ALTER TRIGGER	dbo.tr_patd_expired
ON		dbo.Patd  
FOR		INSERT,
		UPDATE  
AS  

SET NOCOUNT ON;

IF UPDATE(DeathCodeID) OR UPDATE(ExpiredDate)
	MERGE	dbo.Expire AS tgt
	USING	(
			SELECT		i.ExpiredDate AS DateExpired,
					i.DeathCodeID AS IdCauseOfDeath,
					i.IdClinicNumber,
					i.IdPatient  
			FROM		INSERTED AS i
			LEFT JOIN	DELETED AS d ON d.IdClinicNumber = i.IdClinicNumber
						AND d.IdPatd = i.IdPatd
			WHERE		(i.DeathCodeID IS NOT NULL OR i.ExpiredDate IS NOT NULL)  
					AND	(
							ISNULL(i.DeathCodeID, 0) <> ISNULL(d.DeathCodeID, 0)   
							OR ISNULL(i.ExpiredDate, '19000101') <> ISNULL(d.ExpiredDate, '19000101')
						)  
		) AS src ON src.IdPatient = tgt.IdPatient
	WHEN	MATCHED
			THEN	UPDATE
				SET	tgt.DateExpired = src.DateExpired,
					tgt.IdCauseOfDeath = src.IdCauseOfDeath,
					tgt.idClinicNumber = src.idclinicnumber
	WHEN	NOT MATCHED BY TARGET
			THEN	INSERT	(
						DateExpired,
						IdCauseOfDeath,
						idClinicNumber, 
						IdPatient
					)
				VALUES	(
						src.DateExpired,
						src.IdCauseOfDeath,
						src.idClinicNumber, 
						src.IdPatient
					);
GO

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources