Difference between revisions of "DdlDatabaseTriggerLog (database trigger)"
Line 138: | Line 138: | ||
| Insert | | Insert | ||
| Table | | Table | ||
− | | [[dbo.DatabaseLog_ | + | | [[dbo.DatabaseLog_(table)|dbo.DatabaseLog]] |
|} | |} |
Latest revision as of 23:15, 23 June 2010
automatically generated[edit]
databasetrigger | ddlDatabaseTriggerLog |
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;
Dependency Type | Object Type | Referenced Object |
Insert | Table | dbo.DatabaseLog |
wikibot[edit]
Database Trigger | ddlDatabaseTriggerLog |
Description | Database trigger to audit all of the DDL changes made to the AdventureWorks database. |
Source[edit]
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;
References[edit]
Dependency Type | Object Type | Referenced Object |
Insert | Table | dbo.DatabaseLog |