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

[Guest Jose_Manuel_Jurado]

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)

 

Continue reading...