Sample script below:

   1: -- ==========================================================================
   2: -- Object       : DDLTrigger.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Notes        : 
   6: -- ==========================================================================
   7:
   8: -- DDL Triggers
   9: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d79e5725-adea-4934-9474-9cd975adb6d8.htm
  10:
  11: -- DDL Trigger Events
  12: -- http://msdn.microsoft.com/en-us/library/ms189871.aspx
  13:
  14: -- EVENTDATA
  15: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/03a80e63-6f37-4b49-bf13-dc35cfe46c44.htm
  16: -- EVENTDATA function returns XML data which contains the information about server 
  17: -- or database events. 
  18:
  19: -- sample EVENTDATA()
  20: /*
  21: <EVENT_INSTANCE>
  22:   <EventType>CREATE_TABLE</EventType>
  23:   <PostTime>2008-10-05T01:23:12.030</PostTime>
  24:   <SPID>55</SPID>
  25:   <ServerName>JUBILEESQL01</ServerName>
  26:   <LoginName>JUBILEEAdministrator</LoginName>
  27:   <UserName>dbo</UserName>
  28:   <DatabaseName>AdventureWorks</DatabaseName>
  29:   <SchemaName>dbo</SchemaName>
  30:   <ObjectName>t</ObjectName>
  31:   <ObjectType>TABLE</ObjectType>
  32:   <TSQLCommand>
  33:     <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
  34:                 QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
  35:     <CommandText>CREATE TABLE t
  36:         (
  37:             id int
  38:         )
  39: </CommandText>
  40:   </TSQLCommand>
  41: </EVENT_INSTANCE>
  42:
  43:  */
   44:
  45: -- ---------------------------------------------------------------------
  46: -- database scope
  47: -- SSMS: Database > Programmability > Database Triggers
  48: -- ---------------------------------------------------------------------
  49: USE AdventureWorks
  50: GO
  51:
  52: IF EXISTS(
  53:   SELECT *
  54:     FROM sys.triggers
  55:    WHERE name = N'tr_db_ddl_NoCreateTable'
  56:      AND parent_class_desc = N'DATABASE'
  57: )
  58: BEGIN
  59:     DROP TRIGGER tr_db_ddl_NoCreateTable ON DATABASE
  60: END
  61: GO
  62:
  63: CREATE TRIGGER tr_db_ddl_NoCreateTable
  64: ON DATABASE -- current database
  65: FOR CREATE_TABLE, DROP_TABLE
  66: AS
  67: BEGIN
  68:     SELECT
  69:         'tr_db_ddl_NoCreateTable' 'Trigger',
  70:         'You are not permitted to perform selected action' 'Msg2',
  71:         EVENTDATA() 'EVENTDATA()'
  72:     ROLLBACK
  73: END
  74: GO
  75:
  76: -- test
  77: CREATE TABLE t
  78: (
  79:     id int
  80: )
  81: GO
  82:
  83:
  84: -- ---------------------------------------------------------------------
  85: -- server scope
  86: -- SSMS: Server Objects > Database Triggers
  87: -- ---------------------------------------------------------------------
  88: USE AdventureWorks
  89: GO
  90:
  91: IF EXISTS(
  92:    SELECT *
  93:    FROM sys.server_triggers
  94:    WHERE name = N'tr_srv_ddl_DisplayServerEvents'
  95: )
  96: BEGIN
  97:     DROP TRIGGER tr_srv_ddl_DisplayServerEvents ON ALL SERVER
  98: END
  99: GO
 100:
 101:
 102: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
 103: ON ALL SERVER
 104: FOR CREATE_DATABASE, CREATE_LOGIN
 105: AS
 106: BEGIN
 107:     SELECT
 108:         'tr_srv_ddl_DisplayServerEvents' 'Trigger',
 109:         'You are not permitted to perform selected action' 'Msg',
 110:         EVENTDATA() 'EVENTDATA()'
 111:     ROLLBACK
 112: END
 113: GO
 114:
 115: -- test
 116: CREATE DATABASE db
 117: GO

It is also interesting to note that creating a server-scoped trigger for DDL_SERVER_SECURITY_EVENTS

Msg 1082, Level 15, State 1, Procedure tr_srv_ddl_DisplayServerEvents, Line 11
“DROP_SERVER_ROLE_MEMBER” does not support synchronous trigger registration.

The trigger that fails follows:

   1: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
   2: ON ALL SERVER
   3: FOR DDL_SERVER_SECURITY_EVENTS
   4: AS
   5: BEGIN
   6:     SELECT
   7:         'tr_srv_ddl_DisplayServerEvents' 'Trigger',
   8:         'You are not permitted to perform selected action' 'Msg',
   9:         EVENTDATA() 'EVENTDATA()'
  10:     ROLLBACK
  11: END
  12: GO
  13:

Not sure why. Seems the DDL_SERVER_SECURITY_EVENTS should include only server-scoped events.

DDL Trigger Events

Will continue to dig in; when I find additional details I will post a follow up entry to this one.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
Be Sociable, Share!
  • Tweet