Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "CREATE TRIGGER" - Creating a DDL Trigger

By: FYIcenter.com

(Continued from previous topic...)

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

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 FyiCenterData;
GO

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

ALTER TABLE fyi_users 
  ALTER COLUMN id INT NOT NULL;
GO
Someone is changing tables!

(Continued on next topic...)

1. What Are Triggers?
2. What Are the Basic Features of a Trigger?
3. How To Create a Simple Table to Test Triggers?
4. How To Create a DML Trigger using CREATE TRIGGER Statements?
5. How To Test a DML Trigger?
6. How To List All Triggers in the Database with sys.triggers?
7. How To Modify Existing Triggers using "ALTER TRIGGER"?
8. How To Delete Existing Triggers using "DROP TRIGGER"?
9. How To Get the Definition of a Trigger Back?
10. How To Disable Triggers using "DISABLE TRIGGER"?
11. How To Create a Trigger for INSERT Only?
12. How To See the Event List of an Existing Trigger using sys.trigger_events?
13. How To Access the Inserted Record of an Event?
14. How To Access the Deleted Record of an Event?
15. How To Improve the Trigger to Handle NULL Values?
16. What Happens to a Trigger with Multiple Affected Rows?
17. How To Override DML Statements with Triggers?
18. How To Create a DDL Trigger using "CREATE TRIGGER" Statements?
19. Can You Roll Back the DDL Statement in a Trigger?
20. Can You Create a Logon Trigger in SQL Server 2005 Express Edition?