DdlDatabaseTriggerLog (database trigger) - Revision history

Bot at 22:15, 23 June 2010

2010-06-23T22:15:03Z

Bot at 21:59, 23 June 2010

2010-06-23T21:59:13Z

Bot at 07:21, 11 February 2010

2010-02-11T07:21:22Z

Bot at 22:08, 2 February 2010

2010-02-02T22:08:38Z

Bot at 06:01, 30 November 2009

2009-11-30T06:01:22Z

Bot at 05:39, 30 November 2009

2009-11-30T05:39:35Z

Bot at 05:26, 30 November 2009

2009-11-30T05:26:01Z

Bot at 22:58, 26 November 2009

2009-11-26T22:58:43Z

Bot: New page: == automatically generated == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''databasetrigger | .ddlDatabaseTriggerLog |- |}

  CREATE TRIGGER...

2009-11-26T22:27:54Z

New page: == automatically generated == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''databasetrigger | .ddlDatabaseTriggerLog |- |} <pre> CREATE TRIGGER...

New page

== automatically generated ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
| '''databasetrigger
| .ddlDatabaseTriggerLog
|-

|}

<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>

@@ Line 138: / Line 138: @@
 | Insert
 | Table
-| [[dbo.DatabaseLog_(_(table))|dbo.DatabaseLog]]
+| [[dbo.DatabaseLog_(table)|dbo.DatabaseLog]]
 |}

@@ Line 72: / Line 72: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
-|-
+|- valign="top"
 | '''Database Trigger
 | ddlDatabaseTriggerLog
@@ Line 78: / Line 78: @@
 | '''Description
 | Database trigger to audit all of the DDL changes made to the AdventureWorks database.
 |}
 === Source ===
@@ Line 137: / Line 135: @@
 | '''Referenced Object
-|-
+|- valign="top"
 | Insert
 | Table
-| [[dbo.DatabaseLog_(table)|dbo.DatabaseLog]]
+| [[dbo.DatabaseLog_(_(table))|dbo.DatabaseLog]]
 |}

← Older revision		Revision as of 07:21, 11 February 2010
Line 69:		Line 69:

	\|}		\|}
−
	== wikibot ==		== wikibot ==

@@ Line 5: / Line 5: @@
 | '''databasetrigger
 | ddlDatabaseTriggerLog
-|- valign="top"
+|-
 |}

@@ Line 4: / Line 4: @@
 |-
 | '''databasetrigger
-| .ddlDatabaseTriggerLog
+| ddlDatabaseTriggerLog
 |- valign="top"
@@ Line 60: / Line 60: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |- style="background:silver"
 | '''Referenced Object
 |-
 | [[dbo.DatabaseLog_(table)|dbo.DatabaseLog]]
-| Table
 |}

← Older revision		Revision as of 22:08, 2 February 2010
Line 57:		Line 57:


		+	{\| 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"
		+	\|-
		+	\| '''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"		{\| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
	\|- style="background:silver"		\|- style="background:silver"

← Older revision		Revision as of 22:58, 26 November 2009
Line 56:		Line 56:
	END;		END;
	</pre>		</pre>
		+
		+
		+	{\| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
		+	\|- style="background:silver"
		+	\| '''Referenced Object
		+	\| '''Object Type
		+	\| '''Dependency Type
		+
		+	\|-
		+	\| [[dbo.DatabaseLog_(table)\|dbo.DatabaseLog]]
		+	\| Table
		+	\| Insert
		+	\|}