Solved

Sql 2008 R2 Trigger - After update on particular field

Posted on 2013-01-18
3
1,009 Views
Last Modified: 2013-01-18
Hi,
I am wanting to send db_mail when my field 'Status' changes value.

My trigger is as below

create TRIGGER [dbo].[SupportCallEmail]
   ON  [dbo].[SupportCall]
   FOR UPDATE
AS
      IF  UPDATE([Status])
            begin
            exec msdb.dbo.sp_send_dbmail
            @profile_name = 'SMTPPROFILE',      -- Get this value from Server configuration
            @recipients = 'SUPPORT@COMPANY.COM',
            @subject = 'Change in Record Statsus',
            @body = 'status updated'
End


The trigger does work but seems to work on any field change, not just the 'Status' field

Any suggestions?

with thanks in advance
James
0
Comment
Question by:jimbohodge71
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38792356
>  IF  UPDATE([Status])

will return true if the column "status" was in the UPDATE statement, it won't tell you if the value actually changed.

so, either you remove the SET Status = xxx from the update statment if the value doesn't change, or you have to consider to check in the code

create TRIGGER [dbo].[SupportCallEmail]
   ON  [dbo].[SupportCall]
   FOR UPDATE
AS
  declare @status_change_count int

  select  @status_change_count = count(*) 
    from INSERTED i
    join DELETED d
       on i.PK_FIELD_OF_SUPPORTCALLTABLE = d.PK_FIELD_OF_SUPPORTCALLTABLE
    where i.Status <> d.Status
 
      IF  @status_change_count  <> 0
            begin
            exec msdb.dbo.sp_send_dbmail
            @profile_name = 'SMTPPROFILE',      -- Get this value from Server configuration
            @recipients = 'SUPPORT@COMPANY.COM',
            @subject = 'Change in Record Statsus',
            @body = 'status updated'
End 

Open in new window


if Status column may be "null", the expression will be a bit different, but let's start with this
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 38792451
use

COLUMNS_UPDATED()  function as it would check for all column in table.

ref:
http://msdn.microsoft.com/en-us/library/ms186329.aspx

Thanks,
Nishant
0
 

Author Closing Comment

by:jimbohodge71
ID: 38792565
Thanks very much both of you for your feedback, Solution One was spot on and I'm up and running with it now.

Cheers :-)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

757 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

25 Experts available now in Live!

Get 1:1 Help Now