Send e-mail when a row changes

Question

I will try to best explain what I would like to do (for simplicity I only included two columns that I am working with):

I have two different tables, dbo.device and dbo.devicestatus.
They are both tied by DeviceDN which is primary key.
On dbo.devicestatus, there are two columns called TonerLowB and TonerLowY that can have a value of either 1 or 0. What I would like to do is have the application (SQLExpress 2008) send an e-mail when one of the rows of TonerLowB or TonerLowY has it's value updated to "1", but not when it has it's value updated to "0" and have that row "merged" with the data from dbo.device. What I managed to do so far is combine the data from dbo.device with the data from dbo.devicestatus and show just values that are greater than 0 as a select statement:

`

select d.DeviceDN, s.TonerLowB, s.TonerLowY
from dbo.Device d,
dbo.DeviceStatus s
where
d.DeviceDN = s.DeviceDN
and s.TonerLowB + s.TonerLowY > 0 ;

`

I then tinkered with sp_send_dbmail to see if I can send the results via mail and I did manage to do that after defining a profile and creating myself a test mail server. I used the following command:

`

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MyMailProfile',
    @recipients = 'alex@testing.ro',
    @query = 'select d.DeviceDN, s.TonerLowB, s.TonerLowY
from dbo.Device d,
dbo.DeviceStatus s
where
d.DeviceDN = s.DeviceDN
and s.TonerLowB + s.TonerLowY > 0 ;' ,
    @subject = 'Your Query Results',
    @attach_query_result_as_file = 1 ,
    @query_result_separator = '|' ;

`

The problem is that this sends me all the lines that have a value greater than 1 and I don't know how to send for each update and just for the updated row instead of this "bulk" method.

I am thinking of adding two new columns to the database db.devicestatus, TonerLowB_LastUpdate and TonerLowY_LastUpdate, both set up as "datetime". Then I wanted to create two triggers that would update TonerLowB_LastUpdate or TonerLowY_LastUpdate with "set gettime()" when TonerLowB or TonerLowY updates it's value and also send an e-mail with the line that changed it's value (using the mail procedure I mentioned above), if that date is older than 3 days. Unfortunately I don't know how to declare this trigger and this is what I am trying to find help on.

Would this be possible and if so, would it be a good (reliable) mechanism?

score 0 · Answer 1 · answered Feb 21 '16 at 14:19

A wise way would be use 'Triggers' in this context.

Take a look at this post to get started on this Using Triggers to do actions after specific values are updated To summarize, define an update trigger on the field:

CREATE TRIGGER TonerValueChanged
   ON  DeviceStatus 
   AFTER UPDATE
AS 
BEGIN
// here you need to check for the condition if the value is updated to 1 and send the mail
END

Now on every value updated, based on your condition, the mail will be triggered for every row update.

Hope this helps.

Send e-mail when a row changes

1 Answers1