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

[Jose_Manuel_Jurado]

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