Solved

Trigger should fire only when one column is changed

Posted on 2014-03-04
4
332 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 29
while loop in html mail format 5 32
Showdialog 8 20
Convert SP in a format for debugging 7 0
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now