Help needed on DDL Triggers in SQL server 2005

Discussion in 'microsoft.public.sqlserver.programming' 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

    Perhaps you need
    CAST(@eventdata.query('data(//EventType)') AS SYSNAME)
     
    Uri Dimant, Feb 13, 2006
    #2
    1. Advertisements

  3. Learner

    Tom Moreau Guest

    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

    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
     
    Tom Moreau, Feb 13, 2006
    #3
  4. Seems some of the XML and Xquery stuff is case sensitive. Below executed with 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, Feb 13, 2006
    #4
  5. Learner

    Learner Guest

    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
     
    Learner, Feb 13, 2006
    #5
  6. Learner

    Learner Guest

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

    ML Guest

    You've misplaced the closing parentheses:

    CONVERT (NVARCHAR(100), cast(@EventData.Query('Data(//EventType)') as
    sysname))


    ML
     
    ML, Feb 13, 2006
    #7
  8. Learner

    Learner Guest

    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
     
    Learner, Feb 13, 2006
    #8
  9. 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)')
     
    Aaron Bertrand [SQL Server MVP], Feb 13, 2006
    #9
  10. Actually, to be totally honest, I have this one commented out:
    And replaced by @@SPID. I think I initially gleaned that code from a sample
    somewhere...
     
    Aaron Bertrand [SQL Server MVP], Feb 13, 2006
    #10
  11. I was removing the CONVERT just in order to narrow down where the error message was returned, and
    thought that you would figure out the rest. The problem seems to be uppercase/lowercase with the
    XQuery stuff. Check out the difference between below two, and try both of them:

    CONVERT (NVARCHAR(100),@EventData.query('data(//EventType)'))
    CONVERT (NVARCHAR(100),@EventData.Query('DATA(//EventType)')):
     
    Tibor Karaszi, Feb 13, 2006
    #11
  12. Learner

    Learner Guest

    I am sorry for not posting the DDL for DDLEVENTLOG. Here it is:

    Create Table DDLEVENTLOG
    (
    EventType nvarchar (100),
    PostTime nvarchar(30),
    SPID NVARCHAR(5),
    SERVERNAME NVARCHAR(100),
    LOGINNAME NVARCHAR(100),
    DATABASENAME NVARCHAR(100),
    SCHEMANAME NVARCHAR(200),
    OBJECTNAME NVARCHAR(100),
    OBJECTTYPE NVARCHAR(100),
    TARGETOBJNAME NVARCHAR(100),
    TARGETOBJTYPE NVARCHAR(100),
    TSQLSTATEMENT NVARCHAR(2000),
    FULLDATA XML
    )

    Aaron,

    I have changed it according to your suggestion and its finally took
    shape as below

    Alter TRIGGER DDLTRIGGER ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
    AS
    DECLARE @eventData XML
    SET @eventData = EventData()
    INSERT DDLEVENTLOG
    (EVENTTYPE
    )
    VALUES
    (
    convert(nvarchar(100),@eventdata.value('(/EVENT_INSTANCE/eventtype)[1]','nvarchar(100)'))

    )

    It ran successfully!

    But to test if its logging the info in DDLEVENTLOG table i just created
    and dropped a test table here is the code

    create table test
    (
    id nvarchar(100)
    )
    go
    drop table test

    Good both statements ran successfully.

    But when i ran the sql select * from DDLEVENTLOG unfortunately i don't
    see any thing in the table!!

    I mean the table is empty. I know that i just tried with the EventType
    column but that doesn't contain any either. I would expect to see
    Alter_Trigger, Create_Table, Drop_Table text under the EventType column
    but that is Null.

    Could you please try on your machine and let me know if its working on
    your machine.
    Thanks
    -L
     
    Learner, Feb 13, 2006
    #12
  13. Learner

    Learner Guest

    HI Tom ,
    I am sorry about that. Here is the DDL for DDLEVENTLOG

    Create Table DDLEVENTLOG
    (
    EventType nvarchar (100),
    PostTime nvarchar(30),
    SPID NVARCHAR(5),
    SERVERNAME NVARCHAR(100),
    LOGINNAME NVARCHAR(100),
    DATABASENAME NVARCHAR(100),
    SCHEMANAME NVARCHAR(200),
    OBJECTNAME NVARCHAR(100),
    OBJECTTYPE NVARCHAR(100),
    TARGETOBJNAME NVARCHAR(100),
    TARGETOBJTYPE NVARCHAR(100),
    TSQLSTATEMENT NVARCHAR(2000),
    FULLDATA XML
    )

    Thanks
    -L
     
    Learner, Feb 13, 2006
    #13
  14. Learner

    Learner Guest

    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
     
    Learner, Feb 13, 2006
    #14
  15. Can you show use the DROP TRIGGER command that you are executing?
     
    Tibor Karaszi, Feb 13, 2006
    #15
  16. Could you please try on your machine and let me know if its working on
    Various corrections. Remember that cAsE is important when dealing with
    XQuery, and that the DDL trigger needs to be enabled.



    USE tempdb
    GO

    CREATE TABLE dbo.DDLEventLog
    (
    EventType nvarchar (100),
    PostTime nvarchar(30),
    SPID NVARCHAR(5),
    SERVERNAME NVARCHAR(100),
    LOGINNAME NVARCHAR(100),
    DATABASENAME NVARCHAR(100),
    SCHEMANAME NVARCHAR(200),
    OBJECTNAME NVARCHAR(100),
    OBJECTTYPE NVARCHAR(100),
    TARGETOBJNAME NVARCHAR(100),
    TARGETOBJTYPE NVARCHAR(100),
    TSQLSTATEMENT NVARCHAR(2000),
    FULLDATA XML
    )
    GO

    CREATE TRIGGER dbo.DDLTrigger
    ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
    AS
    BEGIN
    SET NOCOUNT ON;

    DECLARE @eventData XML;

    SET @eventData = EVENTDATA();

    INSERT dbo.DDLEVENTLOG
    (
    EVENTTYPE
    )
    SELECT
    @eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)');
    END
    GO

    ENABLE TRIGGER dbo.DDLTrigger ON DATABASE;
    GO

    create table test
    (
    id nvarchar(100)
    )
    go
    drop table test
    go


    SELECT EventType FROM dbo.DDLEventLog;
    GO

    DROP TRIGGER dbo.DDLTrigger ON DATABASE;
    GO
    DROP TABLE dbo.DDLEventLog;
    GO
     
    Aaron Bertrand [SQL Server MVP], Feb 13, 2006
    #16
  17. Learner

    Learner Guest

    Tibor,
    This is the sql i am running

    drop trigger DDLTRIGGER

    Thanks
    -L
     
    Learner, Feb 13, 2006
    #17
  18. When you issued the CREATE TRIGGER, do you remember having to specify ON
    DATABASE? You have to repeat for the DROP as well.


    DROP TRIGGER DDLTRIGGER ON DATABASE


    Same goes for ON ALL SERVER triggers.
     
    Aaron Bertrand [SQL Server MVP], Feb 13, 2006
    #18
  19. Learner

    Learner Guest

    Thanks it works now. I am excited with the new stuff the way its
    working. But couldn't realize that XML attributes are case sensitive.

    I have no more doubts regards to this DDL triggers as i could
    accomlished what i was looking.

    I really appreciate all of your expertise and thank you for your
    valuable time.

    thanks
    -L
     
    Learner, Feb 13, 2006
    #19
  20. Learner

    ML Guest

    Is the collation in your database case-sensitive? If it is, try type "Query"
    and "Data" in lower case.


    ML
     
    ML, Feb 13, 2006
    #20
    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.