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

This article is contributed. See the original author and article here.

Today, I worked in 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

DROP TRIGGER [ddl_DROP_trig_database] ON ALL SERVER

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!!

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.