?
Solved

Sql 2008 R2 Trigger - After update on particular field

Posted on 2013-01-18
3
Medium Priority
?
1,059 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 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