DDL - Triggers

DDL triggers are kind of triggers which gets  invoked on DDL events such as (CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements).

DDL triggers can be defined at Database levels (Database triggers) and server levels (server triggers)

Syntax :

Create Trigger [NAME]

ON [Database | ALL SERVER]

[FOR Event_name ]

As

BEGIN

[SQL CODE]

End

Database scope level example :

CREATE TRIGGER [drop_table]  

ON DATABASE  

FOR DROP_Table 

AS  

   RAISERROR ('can''t drop table',10, 1) 

   ROLLBACK 

GO 

Above trigger will not let any tables to be dropped in the databases

DROP TABLE [dbo].[Person_Audit]

GO

can't drop table

Msg 3609, Level 16, State 2, Line 3

The transaction ended in the trigger. The batch has been aborted.

Server scope trigger  example :

CREATE TRIGGER Create_database_trigger 

ON ALL SERVER  

FOR CREATE_DATABASE  

AS  

    PRINT 'Database Created on ' + cast( getdate() as varchar(30))  

   

GO 

CREATE database [rain_or_shine]

Database Created on May  7 2018  2:08AM