For all you XML-phobes that lke DDL triggers and Event Notifications

After speaking yesterday and recanting my story (for about the 100th time) about EventData being XML as a “nefarious plot” to require DBAs to learn XML, I wrote this fairly simple XQuery (actually dead simple XPath) function to change EventData into a rowset. It works based on the fact that you’ve deposited EventData into a table that looks like this:

CREATE TABLE ddl_log (
id int primary key identity,
data XML
);

CREATE TRIGGER mytrig
ON DATABASE
FOR CREATE_TABLE
AS
INSERT ddl_log VALUES(EventData());

This makes your table into a rowset. You could also use a variation of it in your event notification handler, DDL trigger itself. Just leave out the cross apply. I thought I’d seen this before, but can never seem to ever have found it. Now that I’ve done this, ….learn XML why don’t ‘ya… There can only be more of it in future. Cheers.

SELECT id,
Tab.Col.value(‘./EventType[1]’,’nvarchar(50)’) AS ‘EventType’,
Tab.Col.value(‘./PostTime[1]’,’datetime’) AS  ‘PostTime’,
Tab.Col.value(‘./SPID[1]’,’nvarchar(50)’) AS  ‘SPID’,
Tab.Col.value(‘./ServerName[1]’,’nvarchar(50)’) AS  ‘ServerName’,
Tab.Col.value(‘./LoginName[1]’,’nvarchar(50)’) AS ‘LoginName’,
Tab.Col.value(‘./UserName[1]’,’nvarchar(50)’) AS ‘UserName’,
Tab.Col.value(‘./DatabaseName[1]’,’nvarchar(128)’) AS ‘DatabaseName’,
Tab.Col.value(‘./SchemaName[1]’,’nvarchar(128)’) AS ‘SchemaName’,
Tab.Col.value(‘./ObjectName[1]’,’nvarchar(128)’) AS ‘ObjectName’,
Tab.Col.value(‘./ObjectType[1]’,’nvarchar(50)’) AS ‘ObjectType’,
Tab.Col.value(‘./TSQLCommand[1]/CommandText[1]’,’nvarchar(4000)’) AS ‘CommandText’,
Tab.Col.value(‘./TSQLCommand[1]/SetOptions[1]/@ANSI_NULLS’,’nvarchar(3)’) AS ‘ANSI_NULLS_OPTION’,
Tab.Col.value(‘./TSQLCommand[1]/SetOptions[1]/@ANSI_NULL_DEFAULT’,’nvarchar(3)’) AS ‘ANSI_NULL_DEFAULT_OPTION’,
Tab.Col.value(‘./TSQLCommand[1]/SetOptions[1]/@ANSI_PADDING’,’nvarchar(3)’) AS ‘ANSI_PADDING_OPTION’,
Tab.Col.value(‘./TSQLCommand[1]/SetOptions[1]/@QUOTED_IDENTIFIER’,’nvarchar(3)’) AS ‘QUOTED_IDENTIFIER_OPTION’,
Tab.Col.value(‘./TSQLCommand[1]/SetOptions[1]/@ENCRYPTED_OPTION’,’nvarchar(4)’) AS ‘ENCRYPTED_OPTION’
FROM ddl_log
CROSS APPLY
data.nodes(‘/EVENT_INSTANCE’) AS Tab(Col)
GO

— standalone trigger

CREATE TRIGGER mytrig
ON DATABASE
FOR CREATE_TABLE
AS
DECLARE @x XML
SET @x = Eventdata()
SELECT
Tab.Col.value(‘./EventType[1]’,’nvarchar(50)’) AS ‘EventType’,
Tab.Col.value(‘./PostTime[1]’,’datetime’) AS  ‘PostTime’,
— rest of columns deleted for brevity
FROM @x.nodes(‘/EVENT_INSTANCE’) AS Tab(Col)
GO

2 thoughts on “For all you XML-phobes that lke DDL triggers and Event Notifications

  1. Hi Bob,

    Sorry to contact you through this post but the Email link is showing a javascript error and is not working. I have a quick question and appreciate if you could post something related to this.

    I am trying to enable CLR for a particular database. All I found on the internet is using sp_configure which will enable it for the server. There are few hints on doing this through ALTER DATABASE command but nothing specific which I can follow. Could you tell the command which will enable CLR only for a particular database and not for the server.

    Thanks,
    Nauman

  2. Hi Nauman,

    Sorry to take so long in responding (see latest posting).

    CLR is enabled on a per-instance basis, and cannot be enabled per-database. You can enable it through sp_configure as you mention, or through the SQL Server Surface Area Configuration Utility. If you wanted to block CLR per-database, a database trigger that rolls back any “CREATE ASSEMBLY” would do it.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.