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

Published 02-08-2021 06:36 AM 783 Views

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2117762%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23160%3A%20DDL%20Trigger%20FOR%20ALL%20SERVERS%20in%20Azure%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117762%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20an%20interesting%20service%20request%20where%20our%20customer%20asked%20if%20we%20could%20capture%20creation%20and%20deletion%20of%20a%20database%20using%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Ftriggers%2Fddl-triggers%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EDLL%20Trigger%20for%20All%20Servers%3C%2FA%3E%20in%20Azure%20SQL%20Managed%20Instance.%20Besides%20other%20options%20that%20we%20have.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20order%20to%20test%20this%20option%2C%20I%20created%20a%20dummy%20table%20called%20Logs%20that%20will%20content%20the%20operation%20and%20the%20action.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20TABLE%20Logs%20(ACTION%20VARCHAR(200)%2CADDITIONAL_DATA%20VARCHAR(MAX))%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20it%20I%20created%20two%20triggers%3A%20one%20for%20creation%20and%20other%20one%20for%20deletion.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20TRIGGER%20%5Bddl_CREATE_trig_database%5D%0AON%20ALL%20SERVER%0AFOR%20CREATE_DATABASE%0AAS%0Adeclare%20%40results%20varchar(max)%0Adeclare%20%40subjectText%20varchar(max)%0Adeclare%20%40databaseName%20VARCHAR(255)%0ASET%20%40subjectText%20%3D%20'DATABASE%20CREATE%20on%20'%20%2B%20%40%40SERVERNAME%20%2B%20'%20by%20'%20%2B%20SUSER_SNAME()%20%0ASET%20%40results%20%3D%20%0A%20%20(SELECT%20EVENTDATA().value('(%2FEVENT_INSTANCE%2FTSQLCommand%2FCommandText)%5B1%5D'%2C'nvarchar(max)'))%0ASET%20%40databaseName%20%3D%20(SELECT%20EVENTDATA().value('(%2FEVENT_INSTANCE%2FDatabaseName)%5B1%5D'%2C%20'VARCHAR(255)'))%0A%0AINSERT%20INTO%20Logs%20VALUES(%20%40subjectText%2C%40results)%0AGO%0A%0ACREATE%20TRIGGER%20%5Bddl_DROP_trig_database%5D%0AON%20ALL%20SERVER%0AFOR%20DROP_DATABASE%0AAS%0Adeclare%20%40results%20varchar(max)%0Adeclare%20%40subjectText%20varchar(max)%0Adeclare%20%40databaseName%20VARCHAR(255)%0ASET%20%40subjectText%20%3D%20'DATABASE%20DELETED%20on%20'%20%2B%20%40%40SERVERNAME%20%2B%20'%20by%20'%20%2B%20SUSER_SNAME()%20%0ASET%20%40results%20%3D%20%0A%20%20(SELECT%20EVENTDATA().value('(%2FEVENT_INSTANCE%2FTSQLCommand%2FCommandText)%5B1%5D'%2C'nvarchar(max)'))%0ASET%20%40databaseName%20%3D%20(SELECT%20EVENTDATA().value('(%2FEVENT_INSTANCE%2FDatabaseName)%5B1%5D'%2C%20'VARCHAR(255)'))%0A%0AINSERT%20INTO%20Logs%20VALUES(%20%40subjectText%2C%40results)%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20test%20the%20solution%2C%20I%20executed%20the%20following%20statements%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20DATABASE%20PP2%0ADROP%20DATABASE%20PP2%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20retrieve%20the%20details%2C%20after%20executing%20the%20following%20statement%2C%20I%20got%20the%20information%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20*%20FROM%20Logs%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2117762%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20an%20interesting%20service%20request%20where%20our%20customer%20asked%20if%20we%20could%20capture%20creation%20and%20deletion%20of%20a%20database%20using%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Ftriggers%2Fddl-triggers%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EDLL%20Trigger%20for%20All%20Servers%3C%2FA%3E%20in%20Azure%20SQL%20Managed%20Instance.%20Besides%20other%20options%20that%20we%20have.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Feb 10 2021 01:38 PM
Updated by: