Post reply

Database Triggers

  • August 4, 2021 at 5:16 pm

    #3916936

    Hi,

    I need to send out an email every time an alarm level is <> 0. I need this to happen as soon as the data is inserted into the database. I have written a trigger, the problem is it sends out an email every time any record is added to the database not just when the alarm level <> 0. Can someone tell me what I am doing wrong? Thanks in advance.

    ALTER TRIGGER [dbo].[TV Back Image Alarm Alerts]
       ON  [AK_Mid_Torpedo].[dbo].[TorpedoVision Data 2021]
       FOR INSERT
    AS 

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @tableHTML NVARCHAR(MAX);

    SET @tableHTML = 
    N'
    TORPEDO VISION BACK ALARM ALERT
    ' +
    N'<table border = "1">' +
    N'<tr><th>Car ID</th><th>Image Time</th>' +
    N'<th>Back Alarm Level</th><th>Back Alarm Temp</th><th>Direction</th>' +

    CAST ( ( SELECT td = dbo.[TorpedoVision Data 2021].[Car ID], ' ',
    td = dbo.[TorpedoVision Data 2021].[Image Time], ' ', 
     = 'center',
    td = dbo.[TorpedoVision Data 2021].[Back Image Alarm Status], ' ',
     = 'center',
    td = format(dbo.[TorpedoVision Data 2021].[Back Temp F], '#,#'), ' ',
     = 'center',
    td = dbo.[TorpedoVision Data 2021].[Direction Label]

    FROM dbo.[TorpedoVision Data 2021]
    where [Image Time] in (SELECT MAX([Image Time]) from dbo.[TorpedoVision Data 2021]) and [Back Image Alarm Status] <> '0' 
    FOR XML PATH ('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>';

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'AK Alarm emails',
    @recipients = 'mballentine@connors.biz',
    @subject = 'TorpedoVision Alarm Alert',
    @body = @tableHtml,
    @body_format = 'HTML';




  • August 4, 2021 at 5:40 pm

    #3916946

    You should use [inserted] table instead of dbo.[TorpedoVision Data 2021] inside of the trigger.

    and don't send emails from triggers. Insert the data to be sent into a "queue" table and use a job to take data from that table and send emails.

    _____________
    Code for TallyGenerator

  • August 4, 2021 at 6:51 pm

    #3916954

    Thank you, I will try this.

  • August 5, 2021 at 12:40 am

    #3917016

    And don't use the FORMAT function.  Even relatively complicated functionality created by CONVERT is 38 times (in this case) faster than FORMAT.  Take the time to figure out the alternative that uses convert.  It's worth it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 5, 2021 at 12:04 pm

    #3917124

    Thank you both. I got it working!

  • August 5, 2021 at 12:07 pm

    #3917125

    mballentine wrote:

    Thank you both. I got it working!

    Very cool... can you post the code you used to replace the FORMAT function? 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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