Applies to
ApexSQL Source Control
Summary
This article explains how to setup database e-mail account and customize ApexSQL Source Control in order to get e-mail notifications when a schema change occurs in a database.
Description
When a database is linked to a source control repository, both in dedicated and shared database development models, ApexSQL Source Control creates a DDL trigger in a linked database for the dedicated model and a server level trigger for the shared model – which is used to track schema changes, help mark the objects as changed in the Object Explorer pane, enforce database policies, etc. This DDL trigger is important as it provides the functionalities mentioned above but it can be disabled or removed if the user wants to use only the basic functionalities of ApexSQL Source Control e.g. committing changes to the repository and reviewing the history of committed changes. The trigger is also customizable, which means that it can be tweaked by the user to add\remove specific functionalities or enhance the existing ones.
This article will show how to modify the DDL trigger in order to setup an e-mail notification system each time that a database change is detected.
Sending an e-mail from a database can be achieved by executing the sp_send_dbmail procedure. Prerequisite is that the email profile is set on the server level. Here are the steps in detail:
- Set the email profile on the server level. To set the email profile, refer to the Configure the database mail part of this article.
|
Quick tip: You can test the e-mail profile by right-clicking the Database Mail node in the Object Explorer pane and selecting the Send Test E-Mail command |
- Customize the DDL trigger used by ApexSQL Source Control by following the example of code that should be added to the trigger. Code must be added under the declared variables used for the mail parameters:
DECLARE @sqlStatement as nvarchar(max) DECLARE @messageBody as nvarchar(max) SET @sqlStatement = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') SET @messageBody = 'User: ' + @currentUser + ' executed T-SQL command: ' + @sqlStatement EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ApexSQLTest', @recipients = 'marko.radakovic@apexsql.com' , @body = @messageBody, @subject = 'Schema changes in AdventureWorks2014 database'
-
The E-mail notification will be sent each time when the user executes a DDL statement against the database including relevant information previously set in the DDL trigger (In this case, name of the user and the T-SQL statement that he executed)
FAQs
Q: Can I use any e-mail account when creating a database mail profile?
A: Yes, as long as you provide a valid e-mail address, SMTP server and port number.
Q: Can I customize the body of the e-mail notification?
A: Yes, you have all the information from the SQL Server EVENTDATA() function at your disposal. You can include the type of change, changed object name, type or owner etc.
Q: Where in the DDL trigger should I place code that executes sp_send_dbmail procedure?
A: It depends on the specific needs you have. ApexSQL Source Control uses the DDL trigger to enforce policies on the database. If the specific change does not comply with the policies, it will be discarded. If you want to be notified only about the changes that are successfully applied against the database, you should execute the sp_send_dbmail procedure at the very end of the DDL trigger.
Q: Can I set multiple recipients of the e-mail notification?
A: Yes, the @recipients parameter expects a semicolon delimited list of recipients.
Q: Can I receive notifications if specific users made a change to the database?
A: Yes, since the information about the user who made the change can be found in the @currentUser variable. You can apply a simple condition to filter out the users that you do not want to be notified about.
Q: Can I receive notifications if specific object\schema is changed?
A: Yes, similarly to the previous answer, you can filter schemas\objects by placing a condition on the @schema and @object variables in the trigger.
Q: How to stop using the notification system?
A: Comment or delete the call to the sp_send_dbmail procedure in the DDL trigger
