Latest revision |
Your text |
Line 4: |
Line 4: |
| |- | | |- |
| | '''databasetrigger | | | '''databasetrigger |
− | | ddlDatabaseTriggerLog | + | | .ddlDatabaseTriggerLog |
| |- | | |- |
| + | |
| |} | | |} |
| | | |
Line 55: |
Line 56: |
| END; | | END; |
| </pre> | | </pre> |
− |
| |
− |
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Dependency Type
| |
− | | '''Object Type
| |
− | | '''Referenced Object
| |
− |
| |
− | |-
| |
− | | Insert
| |
− | | Table
| |
− | | [[dbo.DatabaseLog_(table)|dbo.DatabaseLog]]
| |
− |
| |
− | |}
| |
− | == wikibot ==
| |
− |
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- valign="top"
| |
− | | '''Database Trigger
| |
− | | ddlDatabaseTriggerLog
| |
− | |- valign="top"
| |
− | | '''Description
| |
− | | Database trigger to audit all of the DDL changes made to the AdventureWorks database.
| |
− | |}
| |
− |
| |
− | === Source ===
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− |
| |
− | === References ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Dependency Type
| |
− | | '''Object Type
| |
− | | '''Referenced Object
| |
− |
| |
− | |- valign="top"
| |
− | | Insert
| |
− | | Table
| |
− | | [[dbo.DatabaseLog_(table)|dbo.DatabaseLog]]
| |
− |
| |
− | |}
| |