SQL Server table data changes trigger the way messages are sent _mssql

Today encountered a problem, the original production system is running healthily, now need to monitor a data table, when adding data, to the administrator to send mail.

After receiving this requirement, a colleague offers a scenario: write triggers that trigger external applications . This is a bold idea, and never wrote such a trigger.

The following is a reference article:

The first method: the trigger invokes the external program . xp_cmdshell

Http://www.jb51.net/article/90714.htm The first method offered is the need to open xp_cmdshell

Open xp_cmdshell first.

Open the perimeter application configurator->

The perimeter Application configurator for the feature->

Instance Name \database engine\xp_cmdshell->

Enable

You can then invoke an external program: Exec xp_cmdshell ' C:\calc.exe '.

The second method: passes the inserted value to. bat. also use the permissions of the xp_cmdshell that invoke the external program

http://www.sqlparty.com/%E9%A2%98%E7%82%BC/2013/08/05/ E5-a6-82-e4-bd-95-e5-9c-a8-e6-9f-90-e8-a1-a8-e6-96-b0-e6-8f-92-e5-85-a5-e6-95-b0-e6-8d-ae-e6-97-b6-e8-a7-a6-e5-8f-91-e6-8 9-a7-e8-a1-8c-e5-a4-96-e9-83-a8-e7-a8-8b-e5-ba-8f-ef-bc-9f.html

In fact, the second method can be classified as the first.

Here's a third way:

SQL ServerE CLR

This approach can take advantage of VS to SQL Server write stored procedures and triggers. Open vs Write code to the admin email.

The fourth method:

SQL Server Management-->database Mail

After you open Database Mail, configure the settings for the outgoing message, and write the trigger directly to send the inserted content through SQL Server.

Create TRIGGER tri_email on 
[dbo].[ Imagegalleries] After
inserts
as
begin
if exists (SELECT * from inserted)
begin
DECLARE @ Content nvarchar (max)
select @content =i.name+ ' | ' +i.imagepath from inserted i;
exec msdb.dbo.sp_send_dbmail @profile_name = ' sqlprofile ',
@recipients =243594541@qq.com ',
@subject = ' sql Server send email by trigger ',
@body = @content
End

This method is the most direct and easiest method.