MS SQL Server Interview Question:

How To Create a DDL Trigger using "CREATE TRIGGER" Statements?

Submitted by: Administrator

A DDL trigger is defined to handle a DDL statement event, like create, alter and drop tables, views, indexes, etc. DDL triggers can be used to generate warning messages on database object changes. The format of creating a DDL trigger should be:

CREATE TRIGGER trigger_name ON DATABASE
AFTER ddl_event_types
AS
statements
GO
-- ddl_event_types are keywords like:
-- CREATE_TABLE, ALTER_TABLE, DROP_TABLE, ...

Below is a simple example of creating a DDL trigger to generate messages on ALTER_TABLE events:

USE GlobalGuideLineDatabase;
GO

CREATE TRIGGER ddl_message ON DATABASE
AFTER ALTER_TABLE
AS
PRINT 'Someone is changing tables!';
GO

ALTER TABLE ggl_users
ALTER COLUMN id INT NOT NULL;
GO
Someone is changing tables!
Submitted by: Administrator