INTERSECT in UPDATE sp

Question

We are using INTERSECT in our WHERE clause for updating a table to bypass the actual update if nothing changes. It seems to be working in other stored procedures but not this one. Below is our sp and I was testing by not changing anything so @MealName, @Instructions and @Verified were not changed from the values in the table. In the 2nd set of code I have the table schema.

ALTER PROCEDURE [dbo].[kd_updtblActualMealsPartial]
    (@MealID        int
    ,@MealName      nvarchar(75)
    ,@Instructions  nvarchar(4000)
    ,@Verified      bit = 0
    ,@UserLoginID   int
    )

AS 
    BEGIN

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

        DECLARE @WasVerified bit;
        DECLARE @PatientID int;

        SELECT @WasVerified = Verified,
               @PatientID = PatientID
          FROM dbo.tblActualMeals 
         WHERE (MealID = @MealID);

        UPDATE dbo.tblActualMeals 
           SET [MealName] = @MealName, 
               [Instructions] = @Instructions, 
               [Verified] = @Verified 
         WHERE ([MealID] = @MealID)
        AND  NOT EXISTS (SELECT MealID, MealName, ISNULL(Instructions,''), Verified
                        INTERSECT
                        SELECT @MealID, @MealName, ISNULL(@Instructions,''), @Verified);

        --if unverifying meal then remove any MealCalendar records for that MealID
        IF @WasVerified = 1 AND @Verified = 0
            BEGIN
                DELETE FROM dbo.tblMealCalendar 
                       WHERE (PatientID = @PatientID) 
                         AND (MealID = @MealID);
            END
    END

CREATE TABLE [dbo].[tblActualMeals](
    [MealID] [int] IDENTITY(1,1) NOT NULL,
    [PatientID] [int] NULL,
    [MealName] [nvarchar](75) NULL,
    [MealDate] [smalldatetime] NULL,
    [Instructions] [nvarchar](4000) NULL,
    [Verified] [bit] NOT NULL,
    [MealNumber] [int] NULL,
    [Caregiver] [bit] NOT NULL,
 CONSTRAINT [PK_tblActualMeals] PRIMARY KEY NONCLUSTERED 
(
    [MealID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblActualMeals] ADD  CONSTRAINT [DF_tblActualMeals_Verified]  DEFAULT ((0)) FOR [Verified]
GO

ALTER TABLE [dbo].[tblActualMeals] ADD  CONSTRAINT [DF_tblActualMeals_MealNumber]  DEFAULT ((0)) FOR [MealNumber]
GO

ALTER TABLE [dbo].[tblActualMeals] ADD  CONSTRAINT [DF_tblActualMeals_Caregiver]  DEFAULT ((0)) FOR [Caregiver]
GO

Answer 1

Still did not work correctly. I added the FROM to intersect as Melissa suggested and modified the trigger as shown below.

Melissa fooled you. Her "correction" was incorrect.

Attached is a script with your tables, procedure and a version of a trigger. It adds one row to the table and then runs the procedure with the same data. No row is returned from the trigger. (Since I don't have the audit table, I commented out the INSERT part.)

64193-slask.txt

Answer 2

Hi @David Chase ，

After checking, you still need to complete the select statement before INTERSECT which means that from table part is missing in below part.

SELECT MealID, MealName, ISNULL(Instructions,''), Verified

Please refer below updated one:

UPDATE dbo.tblActualMeals   
SET [MealName] = @MealName,   
	[Instructions] = @Instructions,   
	[Verified] = @Verified   
WHERE ([MealID] = @MealID)  
AND  NOT EXISTS (SELECT MealID, MealName, ISNULL(Instructions,''), Verified   
				from　dbo.tblActualMeals   --add this row   
				INTERSECT  
				SELECT @MealID, @MealName, ISNULL(@Instructions,''), @Verified);  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Depends on what you mean with "the trigger ran". A trigger is always fired, even if the statement did not affect any rows. For this reason, it can often be a good idea to add a quick exit on the top of the trigger:

IF NOT EXISTS (SELECT * FROM inserted)
   RETURN

Answer 4

Still did not work correctly. I added the FROM to intersect as Melissa suggested and modified the trigger as shown below. I tested running an UPDATE with none of the columns changing and it still created a row in tblActualMealsAudit table.

ALTER TRIGGER [dbo].[trgActualMealsAudit]
    ON [dbo].[tblActualMeals]
    AFTER INSERT, UPDATE
    AS 
        BEGIN
           SET NOCOUNT ON;

           DECLARE @idnumber AS INT;
           SELECT @idnumber = CONVERT (INT, session_context(N'USERID'));

           IF EXISTS (SELECT * FROM deleted)
               BEGIN
                INSERT INTO [dbo].[tblActualMealsAudit]
                    ([MealID]
                    ,[PatientID]
                    ,[MealName]
                    ,[MealDate]
                    ,[Instructions]
                    ,[Verified]
                    ,[MealNumber]
                    ,[Caregiver]
                    ,[UserLoginID]
                    )
                   SELECT 
                    i.MealID,
                    i.PatientID,
                    i.MealName,
                    i.MealDate,
                    i.Instructions,
                    i.Verified,
                    i.MealNumber,
                    i.Caregiver,
                    @idnumber
                   FROM deleted AS i;
               END
           ELSE
               BEGIN
                IF NOT EXISTS (SELECT * FROM inserted)
                    BEGIN
                        RETURN;
                    END

                INSERT INTO [dbo].[tblActualMealsAudit]
                    ([MealID]
                    ,[PatientID]
                    ,[MealName]
                    ,[MealDate]
                    ,[Instructions]
                    ,[Verified]
                    ,[MealNumber]
                    ,[Caregiver]
                    ,[UserLoginID]
                    )
                   SELECT 
                    i.MealID,
                    i.PatientID,
                    i.MealName,
                    i.MealDate,
                    i.Instructions,
                    i.Verified,
                    i.MealNumber,
                    i.Caregiver,
                    @idnumber
                   FROM inserted AS i;
               END
       END