Send an email when a new row inserted

1

Continuing the discussion from Sql job to send an email on table update:
Sorry I was not able to reply throwing error in saving the reply. So continuing the discussion with new thread.
Hi gbritton. Thanks for your reply.

Here is the detailed information

Created a audit table

created update trigger on target tables(2). this triggers will log the information(insert new row) to audit table when target tables data modified.

Created a DML trigger on auditable to send an email when a new row is inserted

Create TRIGGER TR_Insert_AuditTable
ON dbo.AuditTableName

AFTER INSERT, UPDATE, DELETE
as

EXEC msdb.dbo.sp_send_dbmail
@profile_name='XYZ',
@recipients='123@TEST.COM',
@subject='DATA Changed',
@body='bla bla bla'
GO

DML tigger is packaged in to SQL job. and job is running fine and sending an email when new row is inserted into AuditTable.

Now i need the data also in the email alert.i.e new row data/ attachment.

How to receive the data in email. Please advise.

Thanks

2

note that your trigger must handle multiple rows - the trigger (in Microsoft SQL at least ...) is only called once regardless of the number of rows processed in the INSERT / UPDATE / DELETE statement. It seems likely that your Trigger will usually be called with a single row each time, but you still need to allow for multi-row actions (at the least you could raise an error if multiple rows are received by the trigger)

For your EMail routine you would therefore have to loop round each row that the trigger receives. This question is raised quite often and the usual advice is that a trigger needs to be fast, and "slim". Calling a (Synchronous) EMail Stored Procedure, which may sit-and-wait for an SMTP server to say "Hello" to a remote server, retry a few times, is not a good candidate for a trigger.

The usual answer is to use the trigger to save the data into a "queue" (a table for that purpose, or something which will manage a queue like Service Broker). If your trigger is already "copying" the data into an Audit Table one solution might be to have some extra columns in the Audit Table:

Date/Time Created
Date/Time EMail sent
Email Result Code - e.g. 0=Success, non-zero = Error Code

and then have something else periodically (every second or two if you like) check the Audit Table for new rows WHERE [Date/Time EMail sent] IS NULL and then send the email, then update [Data/Time EMail sent] to current date/time, and [Email Result Code] to the return value from sp_send_dbmail. That routine might be somethng like this:

DECLARE @ID int, @ErrNo int, @Col1 ..., @Col2 ..., ...
WHILE 1=1
BEGIN
    SELECT TOP 1 @ID = ID, @Col1 = col1, @Col2 = col2, ...
    FROM YourAuditTable
    WHERE [Date/Time EMail sent] IS NULL
    ORDER BY [Date/Time Created]
    IF @@ROWCOUNT = 0 GOTO NoMoreRowsToProcess

    SELECT @EmailBody = 'Record ID=' + CONVERT(varchar(20), @ID) + ' has changed'
-- Incorporate @Col1, @Col2 etc. in the body of the message as appropraite

    EXEC @ErrNo = msdb.dbo.sp_send_dbmail
        @profile_name='XYZ',
        @recipients='123@TEST.COM',
        @subject='DATA Changed',
        @body=@EmailBody

-- Mark the Email as sent / error code
    UPDATE U
    SET [Date/Time EMail sent] = GetDate()
        [Email Result Code] = @ErrNo
    FROM YourAuditTable AS U
    WHERE ID = @ID
END

NoMoreRowsToProcess:
3

@Kristen, why not just use the "BREAK" command for the while instead of GOTO?

4

Old habit, I suppose everyone has a new enough version of SQL nowadays.

5

While I agree with Kristen's approach, as it keeps the trigger as simple as possible, it should be pointed out that sp_send_dbmail will never wait for a SMTP server as sp_send_dbmail just puts messages into a Service Broker Queue. The mail Service Broker objects are visible within SSMS when mail is enabled.

6

Didn't know that, thanks. Good to know that MS are making good use of Service Broker :slight_smile: