Difference between revisions of "DdlDatabaseTriggerLog (database trigger)"
| (5 intermediate revisions by the same user not shown) | |||
| Line 4: | Line 4: | ||
|- | |- | ||
| '''databasetrigger | | '''databasetrigger | ||
| − | | | + | | ddlDatabaseTriggerLog |
| − | |- | + | |- |
| − | |||
|} | |} | ||
| Line 60: | Line 59: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- style="background:silver" | |- style="background:silver" | ||
| + | | '''Dependency Type | ||
| + | | '''Object Type | ||
| '''Referenced Object | | '''Referenced Object | ||
| − | |||
| − | |||
|- | |- | ||
| + | | Insert | ||
| + | | Table | ||
| [[dbo.DatabaseLog_(table)|dbo.DatabaseLog]] | | [[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 | | Table | ||
| − | | | + | | [[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 |
