Need help on DDL triggers in SQL Server 2005

Discussion in 'microsoft.public.sqlserver.server' started by Learner, Feb 13, 2006.

  1. Learner

    Learner Guest

    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
     
    Learner, Feb 13, 2006
    #1
    1. Advertisements

  2. Learner

    Uri Dimant Guest

    Please don't multipost. I have already answered in .programming group.
     
    Uri Dimant, Feb 13, 2006
    #2
    1. Advertisements

  3. Learner

    Learner Guest

    oh i am sorry for the confusion. I just wanted to many people see my
    post so that i could be solved. Do you want me remove it from this
    group? How ever it remains unsolved.
    Thanks
    -L
     
    Learner, Feb 13, 2006
    #3
  4. You can add multiple newsgroups to the header and then all the answers
    appear as one. So no need to create separate posts.
     
    Andrew J. Kelly, Feb 13, 2006
    #4
  5. Learner

    Learner Guest

    Hi Andrew,
    I am not sure if i understood it right when you said i can add
    multiple news groups to the heaer and then all the answers appear as
    one.

    You mean can i post a question in one group and could make it to be
    available to all the groups so that i could have many people look at my
    posting?

    Please clarify and if i could do some thing like that please take a
    moment to walk me through how do i do that. I have never did like that
    before.
    thanks
    -L
     
    Learner, Feb 13, 2006
    #5
  6. Yes. You can add as many newsgroups to the "Newsgroups:" section of the
    message. If you have Outlook Express you can click the icon to the left of
    the Newsgroups at the top of the message to add other groups. Then you post
    once and it shows up in all that you picked and so do the answers.
     
    Andrew J. Kelly, Feb 13, 2006
    #6
  7. Hi Learner,

    From your heasers, I see that you're using Google Groups to post. In
    Google Groups, you can add extra groups in the "to newsgroups" box. Make
    sure that you spell the names correct. Seperate the groups with commas.

    However, even though crossposting (posting one messages to multiple
    groups at once) is much better than multi-posting (posting several
    independant copies of a message), it is still a feature to use with
    care. Most regulars in this groups will also read the other groups you
    post it to - unless you're posting to groups where your question is
    off-topic!

    To prevent annoying people by having them read the same message multiple
    time, use the following guidelines:

    1. From the available groups, pick one (just one) that suits your
    questions best.
    2. If several groups appear to cover the same subject, pick one of them.
    You might wish to browse the groups to see which has the most good
    answers and post there :)
    3. If your question really falls between two stools, then (and only
    then) it is appropriate to post your question to both. Make sure that
    you post ONE question, crossposted to both groups.

    Things to avoid are:
    1. No excessive crossposting. Two groups is okay, three groups is on the
    edge. More groups is generally too much. Many spamfilters will reject
    messages posted to more than a set number of groups!
    2. Don't post to multiple groups just to increase your exposure. Keep in
    mind that your question is neither more important nor more urgent than
    any of the other questions here (if it was, you'd be paying someone to
    answer it <g>)
     
    Hugo Kornelis, Feb 14, 2006
    #7
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.