Trigger should fire only when one column is changed

javierpdx
javierpdx used Ask the Experts™
on
I have a trigger that I'm trying to get to fire only when DDecision field is updated and meets certain criteria.    The issue is when another field is updated, the trigger still fires.  
USE [Abc]
GO
/****** Object:  Trigger [dbo].[EmailRule1]    Script Date: 3/3/2014 4:18:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[EmailRule1]
   ON [dbo].[Demographics1]
   AFTER UPDATE
AS 

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	
	--Makes sure that the decision was changed
	IF NOT UPDATE(DDecision)	
		RETURN
	
	IF EXISTS(SELECT * FROM inserted a join deleted b on a.RFSID=b.RFSID 
	WHERE ((B.DDecision <> 'RuleA' OR B.DDecision <> 'RuleB') AND b.IsComplete = 0)  AND ((A.DDecision='RuleA' or A.DDecision='RuleB') AND A.IsComplete = 0))

	BEGIN 
	
	DECLARE @newDEmail varchar(64)
	DECLARE @newDFirst varchar(64)
	DECLARE @newDLast varchar(64)
	DECLARE @EmailBody varchar(MAX)
    -- Insert statements for trigger here
	SELECT  @newDEmail=a.DEmail, @newDFirst=a.DFirst,@newDLast=a.DLast from inserted a join deleted b on a.RFSID=b.RFSID WHERE (B.DDecision <> 'RuleA' OR B.DDecision <> 'RuleB')  and (a.DDecision='RuleB' or a.DDecision='RuleA')
	SET @EmailBody = ' Dear '+ @newDFirst + ' '+ @newDLast + ',

Email message
'
	
		EXEC msdb.dbo.sp_send_dbmail
	@profile_name ='Abc', 
	@from_address = '"Abc" <abc@test.com>',
	@reply_to = abc@test.com',
	@copy_recipients = ‘abc@test.com', 
	@blind_copy_recipients = ‘abc@test.com’, 
	@recipients = @newDEmail, 
	@subject = 'Abc Application Eligibility', @body =@EmailBody

	 END

Open in new window

This is my update command used on the front end Gridview that that is associated to a drop down that a user selects within the griview:
UpdateCommand="UPDATE [Demographics1] SET [DWID] = @DWID, [DFirst] = @DFirst, [DLast] = @DLast, [DSubmitDate] = @DSubmitDate, [DDecision] = @DDecision, [DIsActive] = @DIsActive, [IsComplete] = @IsComplete WHERE [RFSID] = @RFSID">

Open in new window


Thanks in advance.  I've been struggling with this.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The problem is that you are looking to the trigger as a function and it isn't a function.
Instead of made a return you should raise an error.
So try to change your return statement by:
raiserror('Don't you update DDecision? ', 10, 1)
Use below snippet to identify whether the DDecision column values is modified or not. If it returns any thing then the column value is modified. Put IF EXSTS condition in your trigger and use it accordingly.

SELECT 
   *
FROM 
    deleted d
        FULL OUTER JOIN 
    inserted i
        on      D.ContactID = I.ContactID 
            AND D.FieldName = I.FieldName
WHERE
     D.DDecision  <> I.DDecision  

Open in new window

Or maybe you can do two things:
1. Change the IF NOT UPDATE() by IF UPDATE and make this if surronding all your code.
2. Ensure that your update statement don't update all fields, because in some languages/platforms all fields are updated even if you just want to update one column. check if there is a propertie on your GridView control to configure this.

Author

Commented:
Thanks!  I did suggestion 1 and it worked (IF UPDATE).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial