Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trigger should fire only when one column is changed

Posted on 2014-03-04
4
Medium Priority
?
346 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 400 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 1600 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

609 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