# DDL Trigger: ddlDatabaseTriggerLog

## Properties

Name | Value
--- | ---
Name | ddlDatabaseTriggerLog
ID | 2121058592
Trigger Type | SQL TRIGGER
Disabled |
Encrypted |
MS Shipped |
Is Not For Replication |
Uses Ansi Nulls |
Uses Quoted Identifier |
Execute As | Caller
Modify Date | 8/8/2010 5:24:12 PM
Creation Date | 8/8/2010 5:21:43 PM

## Extended Properties

Name | Value
--- | ---
MS_Description | Database trigger to audit all of the DDL changes made to the AdventureWorks2008 database. mobi

## Events

Event Type | Event Group Type
--- | ---
CREATE_TABLE | DDL_DATABASE_LEVEL_EVENTS
ALTER_TABLE | DDL_DATABASE_LEVEL_EVENTS
DROP_TABLE | DDL_DATABASE_LEVEL_EVENTS
CREATE_VIEW | DDL_DATABASE_LEVEL_EVENTS
ALTER_VIEW | DDL_DATABASE_LEVEL_EVENTS
DROP_VIEW | DDL_DATABASE_LEVEL_EVENTS
CREATE_INDEX | DDL_DATABASE_LEVEL_EVENTS
ALTER_INDEX | DDL_DATABASE_LEVEL_EVENTS
DROP_INDEX | DDL_DATABASE_LEVEL_EVENTS
CREATE_XML_INDEX | DDL_DATABASE_LEVEL_EVENTS
ALTER_FULLTEXT_INDEX | DDL_DATABASE_LEVEL_EVENTS
CREATE_FULLTEXT_INDEX | DDL_DATABASE_LEVEL_EVENTS
DROP_FULLTEXT_INDEX | DDL_DATABASE_LEVEL_EVENTS
CREATE_SPATIAL_INDEX | DDL_DATABASE_LEVEL_EVENTS
CREATE_STATISTICS | DDL_DATABASE_LEVEL_EVENTS
UPDATE_STATISTICS | DDL_DATABASE_LEVEL_EVENTS
DROP_STATISTICS | DDL_DATABASE_LEVEL_EVENTS
CREATE_SYNONYM | DDL_DATABASE_LEVEL_EVENTS
DROP_SYNONYM | DDL_DATABASE_LEVEL_EVENTS
CREATE_FUNCTION | DDL_DATABASE_LEVEL_EVENTS
ALTER_FUNCTION | DDL_DATABASE_LEVEL_EVENTS
DROP_FUNCTION | DDL_DATABASE_LEVEL_EVENTS
CREATE_PROCEDURE | DDL_DATABASE_LEVEL_EVENTS
ALTER_PROCEDURE | DDL_DATABASE_LEVEL_EVENTS
DROP_PROCEDURE | DDL_DATABASE_LEVEL_EVENTS
CREATE_TRIGGER | DDL_DATABASE_LEVEL_EVENTS
ALTER_TRIGGER | DDL_DATABASE_LEVEL_EVENTS
DROP_TRIGGER | DDL_DATABASE_LEVEL_EVENTS
CREATE_EVENT_NOTIFICATION | DDL_DATABASE_LEVEL_EVENTS
DROP_EVENT_NOTIFICATION | DDL_DATABASE_LEVEL_EVENTS
CREATE_ASSEMBLY | DDL_DATABASE_LEVEL_EVENTS
ALTER_ASSEMBLY | DDL_DATABASE_LEVEL_EVENTS
DROP_ASSEMBLY | DDL_DATABASE_LEVEL_EVENTS
CREATE_TYPE | DDL_DATABASE_LEVEL_EVENTS
DROP_TYPE | DDL_DATABASE_LEVEL_EVENTS
CREATE_CERTIFICATE | DDL_DATABASE_LEVEL_EVENTS
ALTER_CERTIFICATE | DDL_DATABASE_LEVEL_EVENTS
DROP_CERTIFICATE | DDL_DATABASE_LEVEL_EVENTS
CREATE_USER | DDL_DATABASE_LEVEL_EVENTS
ALTER_USER | DDL_DATABASE_LEVEL_EVENTS
DROP_USER | DDL_DATABASE_LEVEL_EVENTS
ADD_ROLE_MEMBER | DDL_DATABASE_LEVEL_EVENTS
DROP_ROLE_MEMBER | DDL_DATABASE_LEVEL_EVENTS
CREATE_ROLE | DDL_DATABASE_LEVEL_EVENTS
ALTER_ROLE | DDL_DATABASE_LEVEL_EVENTS
DROP_ROLE | DDL_DATABASE_LEVEL_EVENTS
CREATE_APPLICATION_ROLE | DDL_DATABASE_LEVEL_EVENTS
ALTER_APPLICATION_ROLE | DDL_DATABASE_LEVEL_EVENTS
DROP_APPLICATION_ROLE | DDL_DATABASE_LEVEL_EVENTS
CREATE_SCHEMA | DDL_DATABASE_LEVEL_EVENTS
ALTER_SCHEMA | DDL_DATABASE_LEVEL_EVENTS
DROP_SCHEMA | DDL_DATABASE_LEVEL_EVENTS
GRANT_DATABASE | DDL_DATABASE_LEVEL_EVENTS
DENY_DATABASE | DDL_DATABASE_LEVEL_EVENTS
REVOKE_DATABASE | DDL_DATABASE_LEVEL_EVENTS
ALTER_AUTHORIZATION_DATABASE | DDL_DATABASE_LEVEL_EVENTS
CREATE_SYMMETRIC_KEY | DDL_DATABASE_LEVEL_EVENTS
ALTER_SYMMETRIC_KEY | DDL_DATABASE_LEVEL_EVENTS
DROP_SYMMETRIC_KEY | DDL_DATABASE_LEVEL_EVENTS
CREATE_ASYMMETRIC_KEY | DDL_DATABASE_LEVEL_EVENTS
ALTER_ASYMMETRIC_KEY | DDL_DATABASE_LEVEL_EVENTS
DROP_ASYMMETRIC_KEY | DDL_DATABASE_LEVEL_EVENTS
ADD_SIGNATURE_SCHEMA_OBJECT | DDL_DATABASE_LEVEL_EVENTS
DROP_SIGNATURE_SCHEMA_OBJECT | DDL_DATABASE_LEVEL_EVENTS
ADD_SIGNATURE | DDL_DATABASE_LEVEL_EVENTS
DROP_SIGNATURE | DDL_DATABASE_LEVEL_EVENTS
CREATE_MASTER_KEY | DDL_DATABASE_LEVEL_EVENTS
ALTER_MASTER_KEY | DDL_DATABASE_LEVEL_EVENTS
DROP_MASTER_KEY | DDL_DATABASE_LEVEL_EVENTS
CREATE_DATABASE_ENCRYPTION_KEY | DDL_DATABASE_LEVEL_EVENTS
ALTER_DATABASE_ENCRYPTION_KEY | DDL_DATABASE_LEVEL_EVENTS
DROP_DATABASE_ENCRYPTION_KEY | DDL_DATABASE_LEVEL_EVENTS
CREATE_DATABASE_AUDIT_SPECIFICATION | DDL_DATABASE_LEVEL_EVENTS
ALTER_DATABASE_AUDIT_SPECIFICATION | DDL_DATABASE_LEVEL_EVENTS
DROP_DATABASE_AUDIT_SPECIFICATION | DDL_DATABASE_LEVEL_EVENTS
CREATE_MESSAGE_TYPE | DDL_DATABASE_LEVEL_EVENTS
ALTER_MESSAGE_TYPE | DDL_DATABASE_LEVEL_EVENTS
DROP_MESSAGE_TYPE | DDL_DATABASE_LEVEL_EVENTS
CREATE_CONTRACT | DDL_DATABASE_LEVEL_EVENTS
DROP_CONTRACT | DDL_DATABASE_LEVEL_EVENTS
CREATE_QUEUE | DDL_DATABASE_LEVEL_EVENTS
ALTER_QUEUE | DDL_DATABASE_LEVEL_EVENTS
DROP_QUEUE | DDL_DATABASE_LEVEL_EVENTS
CREATE_SERVICE | DDL_DATABASE_LEVEL_EVENTS
ALTER_SERVICE | DDL_DATABASE_LEVEL_EVENTS
DROP_SERVICE | DDL_DATABASE_LEVEL_EVENTS
CREATE_ROUTE | DDL_DATABASE_LEVEL_EVENTS
ALTER_ROUTE | DDL_DATABASE_LEVEL_EVENTS
DROP_ROUTE | DDL_DATABASE_LEVEL_EVENTS
CREATE_REMOTE_SERVICE_BINDING | DDL_DATABASE_LEVEL_EVENTS
ALTER_REMOTE_SERVICE_BINDING | DDL_DATABASE_LEVEL_EVENTS
DROP_REMOTE_SERVICE_BINDING | DDL_DATABASE_LEVEL_EVENTS
CREATE_BROKER_PRIORITY | DDL_DATABASE_LEVEL_EVENTS
ALTER_BROKER_PRIORITY | DDL_DATABASE_LEVEL_EVENTS
DROP_BROKER_PRIORITY | DDL_DATABASE_LEVEL_EVENTS
CREATE_XML_SCHEMA_COLLECTION | DDL_DATABASE_LEVEL_EVENTS
ALTER_XML_SCHEMA_COLLECTION | DDL_DATABASE_LEVEL_EVENTS
DROP_XML_SCHEMA_COLLECTION | DDL_DATABASE_LEVEL_EVENTS
CREATE_PARTITION_FUNCTION | DDL_DATABASE_LEVEL_EVENTS
ALTER_PARTITION_FUNCTION | DDL_DATABASE_LEVEL_EVENTS
DROP_PARTITION_FUNCTION | DDL_DATABASE_LEVEL_EVENTS
CREATE_PARTITION_SCHEME | DDL_DATABASE_LEVEL_EVENTS
ALTER_PARTITION_SCHEME | DDL_DATABASE_LEVEL_EVENTS
DROP_PARTITION_SCHEME | DDL_DATABASE_LEVEL_EVENTS
BIND_DEFAULT | DDL_DATABASE_LEVEL_EVENTS
CREATE_DEFAULT | DDL_DATABASE_LEVEL_EVENTS
DROP_DEFAULT | DDL_DATABASE_LEVEL_EVENTS
UNBIND_DEFAULT | DDL_DATABASE_LEVEL_EVENTS
ALTER_EXTENDED_PROPERTY | DDL_DATABASE_LEVEL_EVENTS
CREATE_EXTENDED_PROPERTY | DDL_DATABASE_LEVEL_EVENTS
DROP_EXTENDED_PROPERTY | DDL_DATABASE_LEVEL_EVENTS
ALTER_FULLTEXT_CATALOG | DDL_DATABASE_LEVEL_EVENTS
CREATE_FULLTEXT_CATALOG | DDL_DATABASE_LEVEL_EVENTS
DROP_FULLTEXT_CATALOG | DDL_DATABASE_LEVEL_EVENTS
ALTER_PLAN_GUIDE | DDL_DATABASE_LEVEL_EVENTS
CREATE_PLAN_GUIDE | DDL_DATABASE_LEVEL_EVENTS
DROP_PLAN_GUIDE | DDL_DATABASE_LEVEL_EVENTS
BIND_RULE | DDL_DATABASE_LEVEL_EVENTS
CREATE_RULE | DDL_DATABASE_LEVEL_EVENTS
DROP_RULE | DDL_DATABASE_LEVEL_EVENTS
UNBIND_RULE | DDL_DATABASE_LEVEL_EVENTS
CREATE_FULLTEXT_STOPLIST | DDL_DATABASE_LEVEL_EVENTS
ALTER_FULLTEXT_STOPLIST | DDL_DATABASE_LEVEL_EVENTS
DROP_FULLTEXT_STOPLIST | DDL_DATABASE_LEVEL_EVENTS
RENAME | DDL_DATABASE_LEVEL_EVENTS

## Objects that depend on [ddlDatabaseTriggerLog]

- ddlDatabaseTriggerLog

## Objects on which [ddlDatabaseTriggerLog] depends

- ddlDatabaseTriggerLog
  - dbo.DatabaseLog

## SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS BEGIN SET NOCOUNT ON; DECLARE @data XML; DECLARE @schema sysname; DECLARE @object sysname; DECLARE @eventType sysname; SET @data = EVENTDATA(); SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'); SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'); SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') IF @object IS NOT NULL PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object; ELSE PRINT ' ' + @eventType + ' - ' + @schema; IF @eventType IS NULL PRINT CONVERT(nvarchar(max), @data); INSERT [dbo].[DatabaseLog] ( [PostTime], [DatabaseUser], [Event], [Schema], [Object], [TSQL], [XmlEvent] ) VALUES ( GETDATE(), CONVERT(sysname, CURRENT_USER), @eventType, CONVERT(sysname, @schema), CONVERT(sysname, @object), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), @data ); END; SET ANSI_NULLS OFF SET QUOTED_IDENTIFIER OFF DISABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database trigger to audit all of the DDL changes made to the AdventureWorks2008 database. mobi' , @level0type=N'TRIGGER',@level0name=N'ddlDatabaseTriggerLog'

#### See also

List of DDL Triggers