Hi every members;
I am new in SQL and i creat an update trigger that send email when a record is updated in my table column and the code is:
CREATE TRIGGER RODTRG01
ON ROD
AFTER UPDATE
AS
BEGIN
IF UPDATE (ROD01) --ROD01 is the column that i need to be notified about its changes--
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'my profile', @recipients = 'my email', @body = 'bla bla bla', @subject = 'notification';
END
END
GO
and lke i said in the code ''ROD01 is the column that i need to be notified about its changes''
this column got one row with only one value 0 or 1
my quetion is how to write;
IF the row (ROD01) = 1 then send message 1
ELSE IF the row (ROD01) = 0 then send message 2
CREATE TRIGGER RODTRG01
ON dbo.ROD
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE (ROD01)
BEGIN
DECLARE @body nvarchar(100)
SET @body = CASE (SELECT TOP (1) ROD01 FROM inserted i)
WHEN 0 THEN 'message 2'
WHEN 1 THEN 'message 1'
ELSE '??' END
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'my profile',
@recipients = 'my email',
@body = @body,
@subject = 'notification';
END
GO --end of trigger
Thank you very very very mach ScottPletcher you solved my problem
good work.
But if you have a litle time to explaine for me in details what mean this code;
DECLARE @body nvarchar(100)
SET @body = CASE (SELECT TOP (1) ROD01 FROM inserted i)
WHEN 0 THEN 'message 2'
WHEN 1 THEN 'message 1'
ELSE '??' END
Please note that if you update multiple rows, in the table, the trigger will only be fired once. Thus Scott's code is designed to only send an email to the first (picked at random) record that is being updated.
The email will be sent even if the [ROD01] column is not changed (but provided it is included in the SET statement of the update, or in an INSERT.
if you only want emails when the value in [ROD01] changes you would need to test for that. (But maybe you just want notification email that the RECORD was change, rather than that the VALUE in [ROD01] has changed?
Personally we never send emails using a trigger. Sending an email is a process which can take enough time to disrupt the original table update, and it may fail etc. (no easy way to log that failure
from the trigger, as it stands). Instead we INSERT a row (or rowS if the trigger is fired for a multiple-row update/insert) into a "SendEmailQueue" table, and have another process listening on that table and sending emails when a new row is added. The "SendEmailQueue" table has a column for "Result" so that we can handle, separately, and failures etc.
A SQL Server trigger automatically has two tables it can read that contain the updates that were done by the statement that caused the trigger to fire (to run). Those tables are called:
inserted
deleted.
The inserted table shows the row(s) after the update has been applied, and the deleted table shows the row(s) before the update was applied.
Your trigger was written to handle only one row being updated at a time. If you ever do update more than one row in a single UPDATE statement, hopefully (presumably) all the ROD01 values will be the same for all those rows.
My code reads the first ROD01 value after it was changed -- since it's using the inserted table -- and tests to see what the value is, either 0 or 1.
Most often you see a variable or column being used directly in the CASE, like this:
CASE @a WHEN 0 THEN 'Msg0' WHEN 1 THEN 'Msg1' END
or
CASE column1 WHEN 1 THEN 'Monday' WHEN 2 THEN 'Tuesday' ...
But SQL allows a SELECT in place of a variable as long as you are careful to read only one value. Therefore, just in case the trigger did happen to update more than one row, you must make sure you read only one of the rows.
So, putting it all together, the TOP (1) reads the first new/updated value from the dbo.ROD table and uses the value of that column to decide what message to send.
Kristen can you elaborate on this a little more. I have the same issue here, as I need to send an email when a specific column in a table changes. However, when I used similar code and fired off the trigger, it basically deadlocked the application using my database and kicked all users out. So basically I have a table that has about 200 rows with a column called "Enabled", which has a value of 0 or 1. When that column changes to 0 in any row, I need to send an email to our team. You are saying there is a better, more safe way to do this, that won't impact performance? Like I said, when I ran my trigger, it kicked all users out of the database with a "record deadlock" error.
Doesn't surprise me, I'm afraid. Probably an all too common solution to the problem, but I don't think it scales well and if there is a problem with the SMTP service then it is quite possible for everything to lock up ...
Instead of sending the email, from the trigger, store the details, of the Email, in a table and have another process "poll" that table looking for any new records, and that process can then actually send the email.
As a side benefit that process can update a row, which it is processing , to "Sending" and then either "Sent" or "Error" - and it can store details of what the error was, making it possible to re-sent any failed emails (assuming they are important enough to warrant it)
You'll need columns for:
From
To
CC
BCC
Subject
Message
all the same as for a normal email, to which you could add:
Date/Time created
Date/Time sent
Status - New, Processing and then either Sent or Error. You could have another value for, for example, "Resend" which you could set (manually) to cause it to be sent again (perhaps after you fix the error - which might be an invalid email address or "destination mailbox full")
Please ScottPletcher i need some more if you can
what about if i would like to be informed about the values of the same column ROD01 but with more than one row
