Bypass UPDATE when nothing changes

David Chase 681 Reputation points
2020-10-14T21:01:06.937+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,251 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 98,731 Reputation points
    2020-10-15T22:04:05.1+00:00

    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)
       
    
    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Viorel 109.8K Reputation points
    2020-10-15T05:34:52.417+00:00

    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 )
    
    1 person found this answer helpful.

  2. CathyJi-MSFT 21,111 Reputation points Microsoft Vendor
    2020-10-15T09:38:55.24+00:00

    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

    0 comments No comments

  3. Jeffrey Williams 1,886 Reputation points
    2020-10-15T21:34:13.55+00:00

    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
            );