Help needed on DDL Triggers in SQL server 2005

Hello,
I am trying to create DDL trigger as below
ALTER TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
/*POSTTIME,
SPID,
SERVERNAME,
LOGINNAME,
DATABASENAME,
SCHEMANAME,
OBJECTNAME,
OBJECTTYPE,
TARGETOBJNAME,
TARGETOBJTYPE,
TSQLSTATEMENT,
FULLDATA*/
)
VALUES
(
CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
/*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
@.EventData*/
)
But when i execute this its throwing an error...
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Am i doing some thing here?
Please help
Thanks
-LPerhaps you need
CAST(@.eventdata.query('data(//EventType)') AS SYSNAME)
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
> Hello,
> I am trying to create DDL trigger as below
>
> ALTER TRIGGER DDLTRIGGER ON DATABASE
> FOR DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.EventData XML
> SET @.EventData = EVENTDATA()
> INSERT DDLEVENTLOG
> (EVENTTYPE
> /*POSTTIME,
> SPID,
> SERVERNAME,
> LOGINNAME,
> DATABASENAME,
> SCHEMANAME,
> OBJECTNAME,
> OBJECTTYPE,
> TARGETOBJNAME,
> TARGETOBJTYPE,
> TSQLSTATEMENT,
> FULLDATA*/
> )
> VALUES
> (
> CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
> /*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
> @.EventData*/
> )
> But when i execute this its throwing an error...
>
> Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
> "Query" is not a valid function, property, or field.
> Am i doing some thing here?
> Please help
> Thanks
> -L
>|||Please post the DDL for DDLEVENTLOG.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
Hello,
I am trying to create DDL trigger as below
ALTER TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
/*POSTTIME,
SPID,
SERVERNAME,
LOGINNAME,
DATABASENAME,
SCHEMANAME,
OBJECTNAME,
OBJECTTYPE,
TARGETOBJNAME,
TARGETOBJTYPE,
TSQLSTATEMENT,
FULLDATA*/
)
VALUES
(
CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
/*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
@.EventData*/
)
But when i execute this its throwing an error...
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Am i doing some thing here?
Please help
Thanks
-L|||Seems some of the XML and Xquery stuff is case sensitive. Below executed wit
h no errors on my
machine:
CREATE TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
)
VALUES
(
@.EventData.query('data(//EventType)')
--CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
> Hello,
> I am trying to create DDL trigger as below
>
> ALTER TRIGGER DDLTRIGGER ON DATABASE
> FOR DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.EventData XML
> SET @.EventData = EVENTDATA()
> INSERT DDLEVENTLOG
> (EVENTTYPE
> /*POSTTIME,
> SPID,
> SERVERNAME,
> LOGINNAME,
> DATABASENAME,
> SCHEMANAME,
> OBJECTNAME,
> OBJECTTYPE,
> TARGETOBJNAME,
> TARGETOBJTYPE,
> TSQLSTATEMENT,
> FULLDATA*/
> )
> VALUES
> (
> CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
> /*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
> @.EventData*/
> )
> But when i execute this its throwing an error...
>
> Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
> "Query" is not a valid function, property, or field.
> Am i doing some thing here?
> Please help
> Thanks
> -L
>|||Uri,
Thanks for the quick reply . But :( no luck yet.
After changing the whole line looks like this
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)')) as
sysname)
but it said
Msg 1035, Level 15, State 10, Procedure DDLTRIGGER, Line 23
Incorrect syntax near 'cast', expected 'AS'.
Am i doing it right here?
Thanks
-L|||Thank you for the quick reply. But i still get the same error on my
machine.
if i run your sql as is i got this
Msg 257, Level 16, State 3, Procedure DDLTRIGGER, Line 6
Implicit conversion from data type xml to nvarchar is not allowed. Use
the CONVERT function to run this query.
and later i uncommented the Convert line and commented out the first
line then i got the same Query error
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Do i need to setup any kind of option on my machine?
Thanks
-L|||You've misplaced the closing parentheses:
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)') as
sysname))
ML
http://milambda.blogspot.com/|||Looks like all of you have no problem running the above sql... But i
still get the same thig.
Here is the entire sql that i am trying to run
CREATE TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.eventData XML
SET @.eventData = EventData()
INSERT DDLEVENTLOG
(EVENTTYPE
)
VALUES
(
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)') as
sysname))
)
Some thing wrong with it?
I still get the Query error
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Thanks
-L|||FWIW, I use @.EventData.Value, not @.EventData.Query. E.g. these work great
for me in a DDL auditing trigger:
@.eventdata.value('(/EVENT_INSTANCE/SPID)[1]','int')
@.eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
@.eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)')|||I got it solved the line, CONVERT
(NVARCHAR(100),@.EventData.query('data(//EventType)')) works!
It doesn't recognise @.EventData.Query but it does @.EventData.query..
I thank all of you for looking into my problem.
How ever i need one more help with dropping a trigger
when i run this sql, drop trigger ddltrigger
i got this message. Is it some kind of security issue? what property do
i need to set inorder to drop it?
Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'ddltrigger', because it does not exist or you
do not have permission.
Thanks
-L