SQL server table data change trigger mail sending method, SQL send mail

SQL server table data change trigger mail sending method, SQL send mail

Today, I encountered a problem where the original production system is running properly. Now I need to monitor a data table and send an email to the Administrator when adding data.

After receiving this requirement, some colleagues provide the following solutions:Trigger triggering external applications. This is a bold idea. I have never written such a trigger.

The following is a reference document:

Method 1: trigger calls an external program. Xp_mongoshell

The first approach provided by http://www.bkjia.com/article/90714.htm is to enable xp_cmdshell

Start xp_cmdshell first

Open the peripheral application configurator->

Function Peripheral application configurator->

Instance name \ Database Engine \ xp_mongoshell->

Enable

Then, you can call the external program Exec xp_mongoshell 'C: \ calc.exe '.

Method 2:Pass the inserted value to. bat.Use the permission to call the xp_cmdshell of an 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-89-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 one.

The third method is described below:

SQL servere CLR

This method can use VS to write stored procedures and triggers to SQL server. Open VS and write code to the Administrator by email.

Method 4:

SQL server Management --> Database Mail

After Database Mail is enabled, configure the Mail settings and write the trigger directly to send the inserted content through SQL server.

Create TRIGGER tri_email ON [dbo].[ImageGalleries]AFTER insertASBEGINif exists(select * from inserted)begindeclare @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=@contentendENDGO 

This method is the most direct and convenient method.