October 17, 2015 at 5:27 am
I have created a trigger like this below:
CREATE TRIGGER TriggerName ON Table_Name AFTER INSERT AS
DECLARE @SERVICE varchar(40)
DECLARE @status varchar(3)
SET @SERVICE=(SELECT [SERVICE] FROM inserted)
SET @status=(SELECT [STATUS] FROM inserted)
IF @status = 'X'
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'cos "' + @SERVICE + '" cos!!'
EXEC msdb.dbo.sp_send_dbmail @recipients=N'moj_adres', @body= @msg, @subject = 'Subject!!!!', @profile_name = 'profil'
END
GO
When I added some a new record to Table_Name with Status='X' nothing happened. I have tried to exec only script below:
EXEC msdb.dbo.sp_send_dbmail @recipients=N'moj_adres', @body= 'test', @subject = 'Subject!!!!', @profile_name = 'profil'
and I received email notification.
Could you give some points which I should verify? Maybe I have to check other settings I do not know...
Thanks for your help.
October 20, 2015 at 8:11 am
k_lewecki (10/17/2015)
I have created a trigger like this below:CREATE TRIGGER TriggerName ON Table_Name AFTER INSERT AS
DECLARE @SERVICE varchar(40)
DECLARE @status varchar(3)
SET @SERVICE=(SELECT [SERVICE] FROM inserted)
SET @status=(SELECT [STATUS] FROM inserted)
IF @status = 'X'
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'cos "' + @SERVICE + '" cos!!'
EXEC msdb.dbo.sp_send_dbmail @recipients=N'moj_adres', @body= @msg, @subject = 'Subject!!!!', @profile_name = 'profil'
END
GO
When I added some a new record to Table_Name with Status='X' nothing happened. I have tried to exec only script below:
EXEC msdb.dbo.sp_send_dbmail @recipients=N'moj_adres', @body= 'test', @subject = 'Subject!!!!', @profile_name = 'profil'
and I received email notification.
Could you give some points which I should verify? Maybe I have to check other settings I do not know...
Thanks for your help.
Please notice that a trigger is not designed for single values. It is set based. so the select from "inserted" may return multiple values.
October 22, 2015 at 2:32 pm
Assuming the login doing the table mod has authority to run sp_send_dbmail, it should work. If not, you might need to look at additional permissions or using EXECUTE AS on the trigger.
Here's an attempt at adjusting the trigger to handle multiple rows at once:
CREATE TRIGGER TriggerName
ON dbo.Table_Name
AFTER INSERT
AS
SET NOCOUNT ON;
DECLARE @msg varchar(7000)
SELECT @msg = STUFF(CAST((
SELECT ',' + i.service
FROM inserted i
WHERE status = 'X'
FOR XML PATH('')
) AS varchar(7000)), 1, 1, '')
IF @msg > ''
BEGIN
SET @msg = 'cos "' + @msg + '" cos!!'
EXEC msdb.dbo.sp_send_dbmail @recipients=N'moj_adres', @body= @msg, @subject = 'Subject!!!!', @profile_name = 'profil'
END --IF
GO --end of trigger
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 27, 2015 at 11:01 pm
I would strongly recommend not to run sending emails or any other kind of external operations from a trigger.
And glitch in the functionality beyond SQL Server control may cause total outage in the database.
You better create a "queue" table, make the trigger to insert ID's of the records to emailed, and then create a job which will send emails for the records found in the queue table.
Then whatever outage happens in emailing system - it will only cause the queue growing, but won't block anything in the database.
_____________
Code for TallyGenerator
October 28, 2015 at 6:13 am
Sergiy (10/27/2015)
I would strongly recommend not to run sending emails or any other kind of external operations from a trigger.And glitch in the functionality beyond SQL Server control may cause total outage in the database.
You better create a "queue" table, make the trigger to insert ID's of the records to emailed, and then create a job which will send emails for the records found in the queue table.
Then whatever outage happens in emailing system - it will only cause the queue growing, but won't block anything in the database.
dbmail is asynchronous, and is already a service broker, with a queue and everything. I'd think it is pretty safe to send an email, assuming permissions to msdb.dbo.sp_sendmail is all set up correctly for all users. Why do you think a separate queue is better? the work for assuring permissions to the queue table would be just about the same concern as permissions to sp_send_dbmail, i would think.
Lowell
October 28, 2015 at 10:12 am
Lowell (10/28/2015)
Sergiy (10/27/2015)
I would strongly recommend not to run sending emails or any other kind of external operations from a trigger.And glitch in the functionality beyond SQL Server control may cause total outage in the database.
You better create a "queue" table, make the trigger to insert ID's of the records to emailed, and then create a job which will send emails for the records found in the queue table.
Then whatever outage happens in emailing system - it will only cause the queue growing, but won't block anything in the database.
dbmail is asynchronous, and is already a service broker, with a queue and everything. I'd think it is pretty safe to send an email, assuming permissions to msdb.dbo.sp_sendmail is all set up correctly for all users. Why do you think a separate queue is better? the work for assuring permissions to the queue table would be just about the same concern as permissions to sp_send_dbmail, i would think.
Yes dbmail is asynch, however I would agree with Sergiy here. I wouldn't like to mix pure DB work of a trigger with external thing like email. Making mailing funcitonalioty separate can prevent errors in the e-mail part of the process from interfering with the original DML of the trigger.
For example, let say sp_sendemail fail for some reason... Do you want your insert fail?
October 28, 2015 at 11:03 am
Eugene Elutin (10/28/2015)
Lowell (10/28/2015)
Sergiy (10/27/2015)
I would strongly recommend not to run sending emails or any other kind of external operations from a trigger.And glitch in the functionality beyond SQL Server control may cause total outage in the database.
You better create a "queue" table, make the trigger to insert ID's of the records to emailed, and then create a job which will send emails for the records found in the queue table.
Then whatever outage happens in emailing system - it will only cause the queue growing, but won't block anything in the database.
dbmail is asynchronous, and is already a service broker, with a queue and everything. I'd think it is pretty safe to send an email, assuming permissions to msdb.dbo.sp_sendmail is all set up correctly for all users. Why do you think a separate queue is better? the work for assuring permissions to the queue table would be just about the same concern as permissions to sp_send_dbmail, i would think.
Yes dbmail is asynch, however I would agree with Sergiy here. I wouldn't like to mix pure DB work of a trigger with external thing like email. Making mailing funcitonalioty separate can prevent errors in the e-mail part of the process from interfering with the original DML of the trigger.
For example, let say sp_sendemail fail for some reason... Do you want your insert fail?
I understand what you're saying, but in reality setting all that up can be a pain.
For now, it might be workable to use the trigger, but have a separate BEGIN TRY ... CATCH around the email send, to capture almost all errors so they don't kill the trigger (yes, certain errors could still kill the trans, but that should be extremely rare).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 30, 2015 at 9:14 pm
ScottPletcher (10/28/2015)
For now, it might be workable to use the trigger, but have a separate BEGIN TRY ... CATCH around the email send, to capture almost all errors so they don't kill the trigger (yes, certain errors could still kill the trans, but that should be extremely rare).
Are you sure about it?
Did you actually try to use TRY ... CATCH inside of a trigger?
It's not a good idea to uset TRY ... CATCH in SQL at all, and it may cause mpore harm than cause inside of a transaction.
Read about using it inside of a trigger before doing this.
For example, here: http://dba.stackexchange.com/questions/8693/why-try-catch-does-not-suppress-exception-in-trigger
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply