General MySQL Oracle SQL Server

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

Q

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

✍: FYIcenter.com

A

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!

 

⇒ Rolling Back the DDL Statement in a Trigger in SQL Server

⇐ "INSTEAD OF" - Overriding DML Statements with Triggers in SQL Server

⇑ Creating and Managing Triggers in SQL Server

⇑⇑ SQL Server Database Tutorials

2016-10-22, 3112🔥, 0💬

General MySQL Oracle SQL Server