Trigger should fire only when one column is changed

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.
javierpdxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gplanaCommented:
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)
0
Easwaran ParamasivamCommented:
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

0
gplanaCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
javierpdxAuthor Commented:
Thanks!  I did suggestion 1 and it worked (IF UPDATE).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.