INSERT Trigger in SQL Server with condition

Question

I'm trying to use an INSERT TRIGGER in an SQL Server db. It's my first trigger an I want to use it to send an email if certain value has been met in that inserted row:

CREATE TRIGGER dbo.SEND_MAIL
    ON  dbo.table
AFTER INSERT
AS 
    BEGIN
        SET NOCOUNT ON
        IF (SELECT field1 FROM INSERTED) = '1'
        BEGIN
          EXEC msdb.dbo.sp_send_dbmail
                @recipients = 'mail address',           
                @profile_name = 'profile1',
                @subject = 'subject', 
                @body = 'body text';
        END
    END

If I try to execute the send_dbmail stored procedure with those email parameters, the email is sent perfectly. But not in the trigger.

I see that values that comply the condition in the IF statement (field1 = '1') are being inserted in the table. But can't figure out why is not working in the trigger.

I tried to use 'LIKE '1'' instead of equal in the IF but no luck.

Any hint to make it work?

Thank you so much in advance,

Sending an email in a `TRIGGER` is a bad idea. Triggers should be as minimal as possible, and sending an email isn't particularly minimal. If the email fails to send as well, then the `INSERT` will fail, which is never going to be desired. Your trigger also *assumes* that an `INSERT` only ever contains 1 row; this is an incorrect belief as they can effect 1+ rows. Put the data you need to email about in a pooling table, and have an automated task send out the email. Or perhaps, even better, have the calling application do the work of sending the email not the RDBMS. — Thom A, Feb 18 '21 at 10:44
Thank you so much for your answer. Instead of my initial approach; will it work if I make the trigger to insert data in a log table that will be used for logging the emails, and in that log table put the trigger (insert) to send the email? If the insert in the log table fails it will fail the insert in the main table aswell? About the numbers, this condition (field1 = 1) should occur really really on low percentage of the records (about 0,2% of total inserted rows) and unfortunately I cannot do that task from the app side. — 1987kess, Feb 18 '21 at 12:09
*"If the insert in the log table fails it will fail the insert in the main table aswell?"* Yes, but the chances of that happening are much lower than an email failure, and if that `INSERT` failures, it's likely that the `INSERT` into the table should as well as there's very likely larger problems afoot. — Thom A, Feb 18 '21 at 12:11
Sending emails programatically has been discussed many, MANY times. Do your research — SMor, Feb 18 '21 at 12:28

score 0 · Accepted Answer · answered Feb 18 '21 at 10:44

You can have many records in the inserted-table, so I would suggest you to write it like:

IF exists (SELECT field1 FROM INSERTED where field1='1') 
    BEGIN
      EXEC msdb.dbo.sp_send_dbmail
            @recipients = 'mail address',           
            @profile_name = 'profile1',
            @subject = 'subject', 
            @body = 'body text';
    END

INSERT Trigger in SQL Server with condition

1 Answers1