DDL Triggers and EventData()

Last Post 23 Apr 2008 12:19 PM by SQLUSA. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dale123
New Member
New Member

--
17 Apr 2008 03:59 AM
Hi - Im trying to make a database log table to record changes to the database schema.
Here is the code i used so far

use movies
go
create table DatabaseChangeRecord
(
LogID int identity(1,1) ,
TimeDate smalldatetime default getdate() ,
UserName varchar(50) default system_user ,
EventType nvarchar(max) ,
TSQL nvarchar(max)
)
go

create trigger DalesTrigger
on database
for create_table , drop_table
as


declare @data xml
set @data=Eventdata()

insert DatabaseChangeRecord (TimeDate , UserName , EventType , TSQL)
values (getdate() , system_user ,
@data.value('(/EVENT_INSTANCE/EVENTTYPE)[1]','nvarchar(max)') ,
@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
go
create table TestTable (id int)
go
drop table TestTable
go
select * from DatabaseChangeRecord

--drop trigger DalesTrigger on database
--drop table DatabaseChangeRecord


The problem is - When i create and drop a simple table and then select all from the database log table

i get values for TimeDate, UserName and TSQL - they are all fine, but if i try any other eventdata() i get nulls in ther column data. For everything other than the TSLQ column!!!

Can anybody help?
Thanks
SQLUSA
New Member
New Member

--
19 Apr 2008 03:58 PM
Try this. Let us know if it works for you.

=====================================================
use tempdb
go
create table DatabaseChangeRecord
(
LogID int identity(1,1) ,
TimeDate smalldatetime default getdate() ,
UserName varchar(50) default system_user ,
EventType nvarchar(max) ,
TSQL nvarchar(max)
)
go

create trigger KalmanTrigger
on database
for create_table , drop_table
as


declare @data xml
set @data=Eventdata()

insert DatabaseChangeRecord (TimeDate , UserName , EventType , TSQL)
values (getdate() , system_user ,
@data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') ,
@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
go
create table TestTable (id int)
go
drop table TestTable
go
select * from DatabaseChangeRecord

--drop trigger KalmanTrigger on database
--drop table DatabaseChangeRecord


=====================================================

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
dale123
New Member
New Member

--
21 Apr 2008 02:17 AM
Thanks for the reply.
The code you posted worked fine - all the fields are there except EventType!!

I changed the code like this - just removed everyting except - eventtype, objectname and server name;

create table DatabaseChangeRecord
(
LogID int identity(1,1) ,
TimeDate smalldatetime default getdate() ,
UserName varchar(50) default system_user ,
EventType nvarchar(max) ,
ServerName nvarchar(max),
ObjectName nvarchar(max)
)
go

create trigger DalesTrigger
on database
for create_table , drop_table
as


declare @data xml
set @data=Eventdata()

insert DatabaseChangeRecord (TimeDate , UserName , EventType , ServerName , ObjectName)
values (getdate() , system_user ,
@data.value('(/EVENT_INSTANCE/EVENTTYPE)[1]','nvarchar(max)') ,
@data.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(max)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
)

go
create table TestTable (id int)
go
drop table TestTable
go
select * from DatabaseChangeRecord

Now i get

TimeDate - Fine
UserName - Fine
EventType - NULL
ServerName - Fine
ObjectName - Fine


I can't seem to see why EventType keeps returning NULL!!
dale123
New Member
New Member

--
21 Apr 2008 02:19 AM
EventType worked this time!!!!!

What was changed - just the database as far as i can see!!!!
SQLUSA
New Member
New Member

--
21 Apr 2008 01:24 PM
This is the difference:

My code: @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') ,
HungarianNotation

Your code: @data.value('(/EVENT_INSTANCE/EVENTYPE)[1]','nvarchar(max)') ,
UPPERCASE

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
dale123
New Member
New Member

--
22 Apr 2008 02:52 AM
Superb - cheers Kalman!!!!

i really didnt think case was so important with SQL
SQLUSA
New Member
New Member

--
23 Apr 2008 12:19 PM
quote:

Originally posted by: Pro Pete
I believe the reason for it being case-sensitive is that EVENTDATA() is xml-based rather than pure SQL Server and so the matching performed during data extraction is probably not controlled by the SQL Collation settings. The OS level and XML are generally case-sensitive.


That maybe one theory Pete. Unix is also case sensitive.

But here is another. CharlesSimonyi the father of HungarianNotation, a RecentSpaceTourist with GirlFriendMarthaStewartPreparingLunchBeforeTheSpaceShotAtBaikonur, got a present from Microsoft staff by the naming of EventData() to be HN_CS (HungarianNotation & CaseSensitive).


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/

You are not authorized to post a reply.

Acceptable Use Policy