Lesson Learned #160: DDL Trigger FOR ALL SERVERS in Azure SQL Managed Instance

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on an interesting service request where our customer asked if we could capture creation and deletion of a database using DLL Trigger for All Servers in Azure SQL Managed Instance. Besides other options that we have. 

 

In order to test this option, I created a dummy table called Logs that will content the operation and the action. 

 

CREATE TABLE Logs (ACTION VARCHAR(200),ADDITIONAL_DATA VARCHAR(MAX))

 

After it I created two triggers: one for creation and other one for deletion. 

 

CREATE TRIGGER [ddl_CREATE_trig_database] ON ALL SERVER FOR CREATE_DATABASE AS declare @results varchar(max) declare @subjectText varchar(max) declare @databaseName VARCHAR(255) SET @subjectText = 'DATABASE CREATE on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() SET @results = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')) SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)')) INSERT INTO Logs VALUES( @subjectText,@results) GO CREATE TRIGGER [ddl_DROP_trig_database] ON ALL SERVER FOR DROP_DATABASE AS declare @results varchar(max) declare @subjectText varchar(max) declare @databaseName VARCHAR(255) SET @subjectText = 'DATABASE DELETED on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() SET @results = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')) SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)')) INSERT INTO Logs VALUES( @subjectText,@results) GO

 

To test the solution, I executed the following statements:

 

CREATE DATABASE PP2 DROP DATABASE PP2

 

To retrieve the details, after executing the following statement, I got the information:

 

SELECT * FROM Logs

 

 

Enjoy!!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.