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)
