Create trigger Error

• soni321

  Old Hand

  Points: 335

  More actions

  July 23, 2012 at 10:47 am

  #261511

  create trigger logsummary on database

  for create_table

  as

  declare @xmleventdata xml

  set @xmleventdata = eventdata()

  insert into tblddleventlog(Eventtime,Eventtype,servername,Databasename,objectivetype,[objective name],username,commandtext)

  select REPLACE(convert(varchar(255),@xmleventdata.query('data(/event_instance/post time)')),'T',' '),

  convert(varchar(255),@xmleventdata.query('data(/event_instance/Eventtype)')),

  convert(varchar(255),@xmleventdata.query('data/event_instance/servername)')),

  convert(varchar(255),@xmleventdata.query('data/event_instance/databasename)')),

  convert(varchar(255),@xmleventdata.query('data/event_instance/objectivetype)')),

  convert(varchar(255),@xmleventdata.query('data/event_instance/objective name)')),

  convert(varchar(255),@xmleventdata.query('data/event_instance/username)')),

  convert(varchar(max),@xmleventdata.query('data/event_instance/Tsqlcommand/commandtext)'))

  Msg 2217, Level 16, State 1, Procedure logsummary, Line 8

  XQuery [query()]: ',' or ')' expected

  i am creating trigger to log the new table created by users

  but it give me error

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  July 23, 2012 at 11:21 am

  #1516365

  A couple things:

  First, you might want to use .data, and not .query (that returns XML).

  Second, XML is case sensitive, so "posttime" will not work. "PostTime" is needed.

  Not entirely sure where this fails, but this works:

  ALTER trigger logsummary on database

  for create_table

  as

  declare @data xml

  set @data = eventdata()

  select

  replace( @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(1000)'), 'T', ' ')

  , @data.value('(/Event_Instance/EventType)[1]', 'nvarchar(1000)')

• soni321

  Old Hand

  Points: 335

  More actions

  July 23, 2012 at 11:26 am

  #1516369

  thanks for reply PostTime works for me