June 4, 2010 at 8:01 am
I have a trigger that fires off on a ALTER_DATABASE, CREATE/DELETE DATABASE. The triggers are firing with no problems. What I'd like to do is find out what database is being touched when the trigger fires off.
For example, if I have a database called "JW_Tools" and a ALTER/DELETE fires off on this database, I'd like to grab "JW_Tools" and be able to populate a variable with it.
Or if someone is CREATING a database called "Ref_Tools", I'd like to capture "Ref_Tools" to use as a variable.
I've tried Select db_name() but that only returns 'master' unless I use a "USE" statement to change my database.
Any ideas?
June 4, 2010 at 10:40 am
Okay....I'm about 95% of the way there. Here's what I've come up with so far.
If I run this....
Select @DBName = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
I get this if I print @DBName....
"ALTER DATABASE [JW_Ref_Tools] MODIFY NAME = [JW_Ref_Tools V2]"
So 1 step further.
If I run this....
Select @DBName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)')
I get this if I print @DBName...
"JW_Ref_Tools"
Now all I need is "JW_Ref_Tools V2" and I'll be set. Anyone know how to get that second argument from the EVENTDATA??
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply