SQL Server trigger

SQL Server trigger

SQL syntax for sending an email using the system stored procedure sp_send_dbmail:

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ] [ , [ @recipients = ] 'recipients [ ; ...n ]' ] [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ] [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ] [ , [ @subject = ] 'subject' ]  [ , [ @body = ] 'body' ]  [ , [ @body_format = ] 'body_format' ] [ , [ @importance = ] 'importance' ] [ , [ @sensitivity = ] 'sensitivity' ] [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ] [ , [ @query = ] 'query' ] [ , [ @execute_query_database = ] 'execute_query_database' ] [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ] [ , [ @query_attachment_filename = ] query_attachment_filename ] [ , [ @query_result_header = ] query_result_header ] [ , [ @query_result_width = ] query_result_width ] [ , [ @query_result_separator = ] 'query_result_separator' ] [ , [ @exclude_query_output = ] exclude_query_output ] [ , [ @append_query_error = ] append_query_error ] [ , [ @query_no_truncate = ] query_no_truncate ] [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

Parameter reference address: https://docs.microsoft.com/en-us/ SQL /relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql

Configure SQL to send emails as follows:

Step 1:

-- Enable the SQL server Mail function exec sp_configure 'show advanced options', 1 goreconfigure; goexec sp_configure 'database Mail XPs ', 1 goreconfigure; go

If the preceding statement fails to be executed, you can also use the following statement.

-- Enable the SQL server Mail function exec sp_configure 'show advanced options', 1 goreconfigure with overridegoexec sp_configure 'database Mail XPs ', 1 goreconfigure with overridego

Use the following statement to check whether the database mail function is enabled successfully and the database configuration information:

-- Query Database Configuration Information select * from sys. configurations -- check whether the database mail function is enabled. If the value is 1, select name, value, description, is_dynamic, is_advancedfrom sys is disabled. configurationswhere name like '% mail %'

Step 2:

If exists (SELECT * FROM msdb .. sysmail_account where name = 'test') -- determines whether the account named test of the email account has a begin EXEC msdb .. sysmail_delete_account_sp @ account_name = 'test' -- delete the account named "test" endexec msdb .. sysmail_add_account_sp -- create an email account @ account_name = 'test' -- email account name, @ email_address = '2017 @ qq.com '-- Sender email address, @ display_name = 'brambling' -- sender name, @ replyto_address = null -- reply address, @ description = null -- email account description, @ mailserver_name = 'smtp .qq.com '-- mail server address, @ mailserver_type = 'smtp' -- mail protocol, @ port = 25 -- mail server port, @ username = '1970 @ qq.com '-- username, @ password = 'xxxxxx' -- password, @ use_default_credentials = 0 -- whether to use the default credential, 0 is NO, 1 is yes, @ enable_ssl = 1 -- whether to enable ssl encryption, 0 is NO, 1 is yes, @ account_id = null -- output parameter, returns the ID of the created email account.

PS: if you are using a QQ mailbox, remember to set the value of @ enable_ssl to 1. Otherwise, a server error will be reported later. It took me a long time to find the cause.

Step 3:

If exists (SELECT * FROM msdb .. sysmail_profile where NAME = n' SendEmailProfile ') -- determines whether the mail configuration file named SendEmailProfile contains begin exec msdb .. sysmail_delete_profile_sp @ profile_name = 'sendemailprofile '-- delete the mail configuration file endexec msdb named SendEmailProfile .. sysmail_add_profile_sp -- add the mail configuration file @ profile_name = 'sendemailprofile ', -- configuration file name @ description = 'database mail configuration file', -- configuration file description @ profile_id = NULL -- output parameter, returns the ID of the created email configuration file.

Step 4:

-- The email account and email configuration file are associated with exec msdb .. sysmail_add_profileaccount_sp @ profile_name = 'sendemailprofile ', -- mail configuration file name @ account_name = 'test', -- mail account name @ sequence_number = 1 -- order of accounts in profile, A configuration file can have multiple different email accounts

Now, the SQL mail configuration is almost complete. Create a trigger to send an email to the user after the user registration is successful.

First, create a table:

-- Create table T_User (UserID int not null identity (128) primary key, UserNo nvarchar (64) not null unique, UserPwd nvarchar () not null, userMail nvarchar (128) null) go

Create an insert-type after trigger:

Create trigger NewUser_Send_Mail on T_User after insert as declare @ UserNo nvarchar (64) declare @ title nvarchar (64) declare @ content nvarchar (320) declare @ mailUrl nvarchar (128) declare @ count int select @ count = COUNT () from inserted select @ UserNo = UserNo, @ mailUrl = UserMail from inserted if (@ count> 0) begin set @ title = 'registration successful notification' set @ content = 'Welcome '+ @ UserNo + '! You have registered successfully! Please do not reply to the notification email! 'Exec msdb. dbo. sp_send_dbmail @ profile_name = 'sendemailprofile ', -- mail configuration file name @ recipients = @ mailUrl, -- mail address @ subject = @ title, -- mail title @ body = @ content, -- mail content @ body_format = 'text' -- type of mail content. text is text and can also be set to html end go.

Let's test it below:

-- Add a new data record to trigger the insert trigger insert into T_User (UserNo, UserPwd, UserMail) values ('demo1', '20140901', '1996 @ qq.com ')

After the preceding statement is executed, the email will be received in about two or three seconds (if no error occurs ). If you have not received the email, you can use the following statement to view the mail sending status.

Use msdbgoselect * from sysmail_allitems -- mail sending status, which can be used to check whether the mail is successfully sent select * from sysmail_mailitems -- select * from sysmail_event_log -- database Mail Log, which can be used to query whether an error is reported

Use msdb go -- Grant the "create user dba for login dba go exec dbo" permission to send database emails to the role named dba. sp_addrolemember @ rolename = 'databasemailuserrole ', @ membername = 'dba 'go use msdb go -- Grant the configuration file email sending permission to the role named dba exec sysmail_add_principalprofile_sp @ principal_name = 'dba ', -- role name @ profile_name = 'sendemailprofile ', -- configuration file name @ is_default = 1 -- for the order of Configuration Files Owned by the role, a database role can have permissions for multiple configuration files

If the role used to log on to the database session does not have the permission to send database mail, an error is returned. Therefore, the preceding SQL statement gives the role the permission to send database emails.

The above is an example of how SQL Server sends an email using a trigger. I hope it will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!