DDL triggers

Building and Optimizing Triggers in SQL Server

Florin Angelescu

Instructor

Definition and properties

DML triggers

  • events associated with DML statements INSERT, UPDATE, DELETE
  • used with AFTER or INSTEAD OF
  • attached to tables or views
  • inserted and deleted special tables

DDL triggers

  • events associated with DDL statements CREATE, ALTER, DROP
  • only used with AFTER
  • attached to databases or servers
  • no special tables
Building and Optimizing Triggers in SQL Server

AFTER and FOR

CREATE TRIGGER DatabaseChangeLog
FOR CREATE_TABLE
[...]
Building and Optimizing Triggers in SQL Server

AFTER and FOR

CREATE TRIGGER TrackDatabaseTables
FOR CREATE_TABLE
[...]

AFTER = FOR

Building and Optimizing Triggers in SQL Server

DDL trigger prerequisites

  • Target object (server or database)
  • Description of the trigger
  • Trigger firing events (DDL)
  • Trigger name
  • DATABASE
  • Log table with definition changes
  • CREATE_TABLE,ALTER_TABLE,DROP_TABLE
  • TrackTableChanges
Building and Optimizing Triggers in SQL Server

DDL trigger definition

CREATE TRIGGER TrackTableChanges

ON DATABASE

FOR CREATE_TABLE,
    ALTER_TABLE,
    DROP_TABLE

AS
    INSERT INTO TablesChangeLog (EventData, ChangedBy)
    VALUES (EVENTDATA(), USER);
Building and Optimizing Triggers in SQL Server

Preventing the triggering events for DML triggers

CREATE TRIGGER PreventTableDeletion
ON DATABASE
FOR DROP_TABLE

AS
    RAISERROR ('You are not allowed to remove tables from this database.', 16, 1);
    ROLLBACK;
Building and Optimizing Triggers in SQL Server

Let's practice!

Building and Optimizing Triggers in SQL Server

Preparing Video For Download...