LISTING 1 - SIMPLE TRIGGER

USE AdventureWorks
GO

-- create a database DDL trigger
CREATE TRIGGER trgDropTable
ON DATABASE
FOR DROP_TABLE
AS
  PRINT 'Table deletion is not allowed.'
  ROLLBACK TRANSACTION
GO

-- create a test table
CREATE TABLE Test(ColA INT)
GO

-- attempt to drop the test table
-- this will fail
DROP TABLE TEST
GO

-- drop the trigger
DROP TRIGGER trgDropTable ON DATABASE
GO

-- attempt to drop the test table
-- this will now succeed
DROP TABLE TEST
GO

LISTING 2 - DROP TABLE PREVENTED

Table deletion is not allowed.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger.
The batch has been aborted.

Listing 3 - Auditing DDL Events

USE AdventureWorks
GO

-- create an audit table
CREATE TABLE DDLAudit
(
  Command   NVARCHAR(1000)
  ,PostUser NVARCHAR(100)
  ,PostTime NVARCHAR(24)
)
GO

-- create a database DDL trigger
CREATE TRIGGER trgDDLAudit
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
  DECLARE @Data      XML
  DECLARE @Command   NVARCHAR(1000)
  DECLARE @PostTime  NVARCHAR(24)
  DECLARE @PostUser  NVARCHAR(100)
  DECLARE @TempXML   XML

  SET @Data = EVENTDATA()

  -- use XQuery to extract command name
  SET @TempXML = 
      @data.query(
      'data(//TSQLCommand/CommandText)')

  SET @Command = 
      CONVERT(NVARCHAR(100), @TempXML) 

  -- use XQuery to extract post time
  SET @TempXML = 
      @data.query('data(//PostTime)')

  SET @PostTime = 
      CONVERT(NVARCHAR(24), @TempXML) 

  SET @PostUser = SYSTEM_USER 

  INSERT INTO DDLAudit
  (
    Command
    ,PostUser
    ,PostTime
  )
  VALUES
  (
    @Command
    ,@PostUser
    ,@PostTime
  )
GO

-- create a test table
CREATE TABLE Test(ColA INT)
GO

-- drop the test table
DROP TABLE TEST
GO

-- review rows in audit table
SELECT * FROM DDLAudit
GO

-- drop the trigger
DROP TRIGGER trgDDLAudit ON DATABASE
GO

-- drop the table
DROP TABLE DDLAudit
GO