Solved

Trigger should fire only when one column is changed

Posted on 2014-03-04
4
336 Views
Last Modified: 2014-03-04
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.
0
Comment
Question by:javierpdx
  • 2
4 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 39904066
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
 
LVL 16

Assisted Solution

by:Easwaran Paramasivam
Easwaran Paramasivam earned 100 total points
ID: 39904156
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
 
LVL 15

Accepted Solution

by:
gplana earned 400 total points
ID: 39904225
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
 

Author Closing Comment

by:javierpdx
ID: 39904525
Thanks!  I did suggestion 1 and it worked (IF UPDATE).
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question