MS SQL Server Concepts and Programming Question:

Download Job Interview Questions and Answers PDF

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

MS SQL Server Interview Question
MS SQL Server Interview Question

Answer:

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!

Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
How To Override DML Statements with Triggers?Can You Roll Back the DDL Statement in a Trigger?