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

   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: