Post reply

Server level DDL trigger for DROP_DATABASE

  • February 14, 2008 at 7:58 am

    #120081

    I have a server level trigger defined like below. I want to capture the database name and only prevent dropping of certain databases. I can't seem to isolate what database is being dropped from the EVENTDATA. This trigger should say that you can't drop the database and print the database name, however, the database name never shows. Any ideas why I am not getting the database name back?

    drop TRIGGER PreventDropDatabase

    ON ALL SERVER

    FOR DROP_DATABASE

    AS

    DECLARE @xmlData AS XML

    print 'you cannot drop this database'

    SET @xmldata = EVENTDATA()

    SELECT @xmlData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')

    ROLLBACK;

  • February 14, 2008 at 8:23 am

    #778377

    Do you get the print message?

    When you do not get the database name, what is the content of the eventdata.

    On my test server your approach seems to works well.

    It is interesting to note that while you can execute a drop database statement with multiple databases in them, the trigger is executed for each database dropped 🙂

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • February 14, 2008 at 8:37 am

    #778387

    If I do this:

    SET @xmldata = EVENTDATA()

    print CAST(@xmldata AS NVARCHAR(MAX))

    SELECT @xmlData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')

    I get this (Note the blank line and 1 row affected - If I print the whole XML, it seems to work, but trying to get the database name isn't working for me - I tried it on 2 different servers w/ the same result):

    (1 row(s) affected)

  • February 14, 2008 at 8:46 am

    #778399

    Triggers returning rowsets is frowned upon. There's a server setting that prohibits triggers from retruning result sets at all.

    I would suggest you stick to print.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 14, 2008 at 9:53 am

    #778452

    I don't even need anything to print really.I'm just printing for debugging purposes. What I'm really trying to do is isolate the database name so that I can prevent a certain database from being dropped, not all databases. I guess I could parse the XML myself, but that just seems silly.

  • February 14, 2008 at 11:05 am

    #778491

    Try print for debug then. Assign a local var in the trigger and print that. If the print prints nothing, then there's something else wrong.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 14, 2008 at 11:42 am

    #778509

    thanks, I didn't expect to see a difference, but the print worked over the select. Thanks for the help!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply