DDL Trigger

DDL triggers executes/Fire in response to a variety of Data Definition Language (DDL) events.

DDL Events -  CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS

Objects-- Table, Function, Index, Stored Procedure, View, etc.

uses:
Prevent certain changes to your database schema.
Track the changes of definition, who did changes & when.

DDL triggers do not create the special inserted and deleted tables.

Database level

Create TRIGGER TR_CheckforAudit

ON DATABASE

FOR DROP_TABLE

AS

BEGIN

  PRINT 'you cannot create table'

  ROLLBACK TRANSACTION

END

CREATE TABLE [dbo].[Sales]

(

[ID] [int] IDENTITY(1,1) NOT NULL,

[CustomerId] [int] NULL,

Amount Decimal(10,2)

)

Drop Table IF Exists [dbo].[Sales]

Error-

you cannot create table

Msg 3609, Level 16, State 2, Line 38

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

club Create, Alter & drop in trigger,

Create TRIGGER TR_CheckforAuditDB

ON DATABASE

FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE

AS

BEGIN 

   PRINT 'you cannot create,alter,drop table'

   ROLLBACK TRANSACTION

END

How to enable and disable trigger, 

DISABLE TRIGGER TR_CheckforAuditDB ON DATABASE

ENABLE TRIGGER TR_CheckforAuditDB ON DATABASE

Drop Trigger

DROP TRIGGER TR_CheckforAuditDB ON DATABASE

--------------------------

DDL events for stored procedure

CREATE TRIGGER TR_CheckforAuditProcedure

ON DATABASE

FOR CREATE_Procedure, ALTER_Procedure, DROP_Procedure

AS

BEGIN 

   PRINT 'you cannot create,alter,drop procedure'

   ROLLBACK TRANSACTION

END

Server Level

Create TRIGGER TR_CheckforAuditServer

ON ALL SERVER

FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE

AS

BEGIN 

   PRINT 'you cannot create,alter,drop table on this server '+ @@SERVICENAME

   ROLLBACK TRANSACTION

END

Above trigger not allow to create, alter, drop table on any database on the server.

CREATE TABLE [dbo].[Salesdata]

(

[ID] [int] IDENTITY(1,1) NOT NULL,

[CustomerId] [int] NULL,

Amount Decimal(10,2)

)

you cannot create,alter,drop table on this server MYCOMP

Msg 3609, Level 16, State 2, Line 38

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

DISABLE TRIGGER TR_CheckforAuditServer ON ALL SERVER

ENABLE TRIGGER TR_CheckforAuditServer ON ALL SERVER 

DROP TRIGGER TR_CheckforAuditServer ON ALL SERVER

--------------------------

How to do Schema Auditing using DDL Trigger

CREATE TABLE AuditTable

(

    AuditTableID INT IDENTITY PRIMARY KEY,

    DatabaseName nvarchar(128),

    TableName nvarchar(128),

    EventType nvarchar(128),

    LoginName nvarchar(128),

    SQLCommand nvarchar(2000),

    CreatedDatetime datetime

)

Go

Trigger Definition:

CREATE TRIGGER tr_Audittablechanges

ON DATABASE

FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE

AS

BEGIN

    DECLARE @EventData XML

    SELECT @EventData = EVENTDATA()

    INSERT INTO practice.dbo.AuditTable

    (DatabaseName, TableName, EventType, LoginName,SQLCommand,CreatedDatetime)

    VALUES

    (

         @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)'),

         @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)'),

         @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'),

         @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)'),

         @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),

         GetDate()

    )

END