Email trigger with multiple records 2

[psychoflea]

Hi

It there a way that I can send an email when multiple records are inserted to a table?

Basically multiple record are inserted into a table and rather than bombard the users with emails for each record I was wondering if I could send a batch of inserts in a single email.
All the record inserted for each batch will share the same BatchID int.

All the examples I have seen only work with one record at a time as they require assigning the values to variables.

Just checking if there is a way to do this via a trigger?

If you have any question that might help to explain better then i'm happy to try and answer them.
Thanks

 

[markros]

What is your SQL Server version? If it's 2005 or up, you don't even need a trigger, you can use OUTPUT clause of the INSERT command and output into an e-mail table. Then you can have a job scheduled that checks the e-mail table periodically and sends the e-mails.

How does it sound?

 

[RiverGuy]

Triggers are not based on one-row. If you have a statement such as "INSERT INTO Table2 SELECT * FROM Table1," and Table1 has ten rows, then your trigger will fire once, not ten times.

But I think what you are saying is that your application or whatever is inserting this data is using logical batches, which mean nothing to the database itself. If your application fires off ten insert statements in a process, then you only want one email? If this process is to be used to alert normal users of new data, why not code this into the application itself instead? If the application is the sole area containing the logic as far as what constitutes a batch, then the application is the proper place for it.

On the other hand, if you are doing these strictly for security or auditing purposes, then there are other, better approaches. I doubt anyone will need to know the instant a row is inserted. Why not set up a normal "Audit table" version of your table that is written to with triggers? Going with what makros said, you can then schedule something to send out emails stating that "in the past 5 minutes, x rows were inserted." Your scheduled job would read from this audit table. If there are no new rows, then your job does not send an email. If this is for security/audit, I would also recommend to use triggers to log to the audit table as opposed to relying on the OUTPUT clause. Security/Audit needs to account for all ways rows enter the table.

 

[psychoflea]

Thanks for your responses.

I thought that might be the answer that I was going to get.

The email side of it is already coded in the application (Access Front Linked to SQL DB), but its causing complications when it tries to interact with Outlook (which is upsetting the users) so i'm getting SQL to do the work instead.

I'm favouring the idea of using a scheduled job to send the email from new data that has been inserted. I can add a flag to track whats been sent and just exclude those records from future emails.

Cheers

 

[cccook]

If by some chance you do have a need for instantaneous notification, you can use something similar to the following in a trigger.

SET @sMsg = ''
SELECT @sMsg = @sMsg + 'My Data: ' + My_Field_1 + ', My Data 2: ' + My_Field_2 + CHAR(13) + CHAR(10)
		FROM inserted WHERE ...

This would handle your multiple rows.