Get database name from DDL Trigger

Question

I am creating a server level trigger in SQL 2008 to log table creation and drops. I need to log the database that the table was created in/dropped from. First I created a column with a default value of db_name(), but this always recorded master. Next I tried using this in my insert statement:

EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')

This worked for a while, but suddenly it started recording master for all table creations and drops regardless of the database the table was in. All of the table drops have been done using SSMS. Does anyone know why I am seeing this behavior? Even more important, does anyone know how to log the correct database?

EDIT: I found an article which makes me think what I'm doing is incorrect. Apparently you should only capture create_table and drop_table from a database scoped trigger and not from a server_scoped trigger. I would still like to leave the question open though in case someone knows how to work around this.

Answer 1

HI,

You are correct, CREATE TABLE and DROP TABLE events should be recorded from within DDL Triggers that are defined at the database level.

Server Level Triggers are intended for server wide events, for example, when a Login occurs.

Here is an excellent article that may assist you in your developments.

http://www.developer.com/db/article.php/3552096

The following refence details which DDL events can be fired at either Database or Server scope.

http://msdn.microsoft.com/en-us/library/ms189871(SQL.90).aspx Cheers,