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'

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'