Difference between revisions of "DdlDatabaseTriggerLog (database trigger)"
| Line 72: | Line 72: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
| − | |- | + | |- valign="top" |
| '''Database Trigger | | '''Database Trigger | ||
| ddlDatabaseTriggerLog | | ddlDatabaseTriggerLog | ||
| Line 78: | Line 78: | ||
| '''Description | | '''Description | ||
| Database trigger to audit all of the DDL changes made to the AdventureWorks database. | | Database trigger to audit all of the DDL changes made to the AdventureWorks database. | ||
| − | |||
|} | |} | ||
| − | |||
=== Source === | === Source === | ||
| Line 137: | Line 135: | ||
| '''Referenced Object | | '''Referenced Object | ||
| − | |- | + | |- valign="top" |
| Insert | | Insert | ||
| Table | | Table | ||
| − | | [[dbo.DatabaseLog_(table)|dbo.DatabaseLog]] | + | | [[dbo.DatabaseLog_(_(table))|dbo.DatabaseLog]] |
|} | |} | ||
Revision as of 22:59, 23 June 2010
automatically generated
| 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
| Database Trigger | ddlDatabaseTriggerLog |
| Description | Database trigger to audit all of the DDL changes made to the AdventureWorks database. |
Source
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
| Dependency Type | Object Type | Referenced Object |
| Insert | Table | dbo.DatabaseLog |
