Bypass UPDATE when nothing changes

Question

Our db admin added triggers to some tables in a SQL 2016 database to INSERT records into an audit table to track changes made. However, in some of the web pages used to update the users will click the Update button even though nothing was changed. This creates a meaningless audit row in the audit table. Is there some way to bypass an update if nothing changes? Below is a sample stored procedure used.

ALTER PROCEDURE [dbo].[kd_updtblPatients]
    @PatientID          int,
    @FirstName          nvarchar(20),
    @MiddleName         nvarchar(20),
    @LastName           nvarchar(30),
    @DOB            smalldatetime,
    @StartDiet          smalldatetime,
    @EndDiet            smalldatetime,
    @Email          nvarchar(100),
    @Gender         char(1),
    @CaregiverLogin     nvarchar(50),
    @CaregiverPW        nvarchar(20),
    @DietDiscontinued       bit,
    @UserLoginID        int,
    @MedRecordNo        varchar(100)

AS 
    SET NOCOUNT ON 

    BEGIN

        --Put login userid into session context for use in trigger(s)
        EXEC sp_set_session_context 'USERID', @UserLoginID, @read_only = 0;

        DECLARE @StatusIDint smallint;
        IF @DietDiscontinued = 1
            BEGIN
                SET @StatusIDint = 0;
            END
        ELSE
            BEGIN
                SET @StatusIDint = 1;
            END

        UPDATE [dbo].[tblPatients]
           SET [FirstName] = @FirstName, 
               [MiddleName] = @MiddleName, 
               [LastName] = @LastName, 
               [DOB] = @DOB, 
               [StartDiet] = @StartDiet, 
               [EndDiet] = @EndDiet, 
               [Gender] = @Gender, 
               [StatusID] = @StatusIDint,
               [MedRecordNo] = @MedRecordNo
         WHERE ([PatientID] = @PatientID);

    END

Answer 1

As you noted, the other solutions does not work as intended if there are NULL values involved. Here is one that does:

Update [dbo].[tblPatients]
     Set [FirstName] = @FirstName
       , [MiddleName] = @MiddleName
       , [LastName] = @LastName
       , [DOB] = @DOB
       , [StartDiet] = @StartDiet
       , [EndDiet] = @EndDiet
       , [Gender] = @Gender
       , [StatusID] = @StatusIDint
       , [MedRecordNo] = @MedRecordNo
   Where PatiendID = @PatientID
    AND  NOT EXISTS (SELECT PatientID, FirstName, MiddleName, LastName, DOB, StartDiet,
                            EndDiet, Gender, StatusID, MedRecordNo
                     INTERSECT
                     SELECT @PatientID, @FirstName, @MiddleName, @LastName, @DOB, @StartDiet,
                            @EndDiet, @Gender, @StatusID, @MedRecordNo)
   

Answer 2

Maybe the naïve approach to try is:

UPDATE [dbo].[tblPatients]
            SET [FirstName] = @FirstName, 
                [MiddleName] = @MiddleName, 
                [LastName] = @LastName, 
                [DOB] = @DOB, 
                [StartDiet] = @StartDiet, 
                [EndDiet] = @EndDiet, 
                [Gender] = @Gender, 
                [StatusID] = @StatusIDint,
                [MedRecordNo] = @MedRecordNo
          WHERE [PatientID] = @PatientID
          and not (
                [FirstName] = @FirstName and 
                [MiddleName] = @MiddleName and
                [LastName] = @LastName and
                [DOB] = @DOB and
                [StartDiet] = @StartDiet and
                [EndDiet] = @EndDiet and 
                [Gender] = @Gender and 
                [StatusID] = @StatusIDint and
                [MedRecordNo] = @MedRecordNo )

Answer 3

Hi @David Chase ,

You can also try other methods to audit database changes.

Refer to Various techniques to audit SQL Server databases.

Best regards,
Cathy

Answer 4

Something like this?

 Update [dbo].[tblPatients]
    Set [FirstName] = @FirstName
      , [MiddleName] = @MiddleName
      , [LastName] = @LastName
      , [DOB] = @DOB
      , [StartDiet] = @StartDiet
      , [EndDiet] = @EndDiet
      , [Gender] = @Gender
      , [StatusID] = @StatusIDint
      , [MedRecordNo] = @MedRecordNo
  Where [PatientID] = @PatientID
    And (
        [FirstName] <> @FirstName
     Or [MiddleName] <> @MiddleName
     Or [LastName] <> @LastName
     Or [DOB] <> @DOB
     Or [StartDiet] <> @StartDiet
     Or [EndDiet] <> @EndDiet
     Or [Gender] <> @Gender
     Or [StatusID] <> @StatusIDint
     Or [MedRecordNo] <> @MedRecordNo
        );