Trigger and email notification

  • 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.

  • 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.

  • 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) A socialist is someone who give you the shirt off *someone else's* back.

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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) A socialist is someone who give you the shirt off *someone else's* back.

  • 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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply