Server level DDL trigger for DROP_DATABASE

Question

Post reply

Server level DDL trigger for DROP_DATABASE

Adam Angelini

SSCrazy Eights

Points: 8548
More actions
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;

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

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 2

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 4

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 6

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