Lesson Learned #309: Create a DDL Trigger on Database in Azure SQL

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Today, I've an interested way to add an additional logical at the moment that we create or drop a table, basically using, 

CREATE TRIGGER [XZY] ON DATABASE AFTER create_table

For example, we could create this trigger to prevent that our users don't create table without using in the name of the table the prefix test.

CREATE TRIGGER [TR_DDL_AFTER_TABLE] ON DATABASE
after create_table
as
 declare @AffectedTable nvarchar(255)
 Select @AffectedTable = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)')
 declare @AffectedSchema nvarchar(255)
 Select @AffectedSchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)')

 IF @AffectedSchema = 'dbo' and @AffectedTable not like 'test%' 
	rollback transaction

Using this trigger when any user tries to create a table in the dbo schema without using the prefix test, SQL Server will report an error. Create the table testxyz will be possible but if we try other name like tesxyz will report an error and the transaction will be rollack.

CREATE TABLE [dbo].[testxyz] ([schema_major_version] integer NOT NULL, [schema_minor_version] integer NOT NULL, [schema_extended_info] nvarchar(100) NOT NULL)