Sys.Databases

Question

Sys.Databases

Steve Malcolm

Valued Member

Points: 65
More actions
September 28, 2007 at 5:10 pm

#125862

I am trying to determine if it's possible to create a trigger on sys.databases. Everything that we have tried has failed.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

That's because you can't do it. I'm here assuming that you want to figure out when a new db is created to be able to put in place a maintenance plan.

For that, you can have a trace run for that special DB event. There might also be a ddl even that you could track in a ddl trigger. Check out BOL and let us know what you found out.

Steve Malcolm Valued Member Points: 65 More actions · Answer 2

Very close. I want to monitor this view so that when the database is finished restoring, and the value in the sys.databases 'State' column returns to 1, I can execute a stored procedure.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Just run that procedure after the code that runs the restore has done executing.

Steve Malcolm Valued Member Points: 65 More actions · Answer 4

That's under consideration, but we wanted to try it with a trigger if it was possible. Is it impossible because sys.databases is a view?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

System table are out of range for such processes. It could be done in 2000 but it was extremely hard, hazardous, dangerous, at best. Not to mention that you broke the agreement with MS and their support by doing that. I'd go with the other options with any versions.

Steve Malcolm Valued Member Points: 65 More actions · Answer 6

Thanks. Your wise counsel has be recorded and minded.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

HTH.

Why exactly did you want to do this process async from the other operations?

Steve Malcolm Valued Member Points: 65 More actions · Answer 8

More than anything else, this was an attempt to keep processes required by different teams apart from each other's. The "restoring" process belongs to our DBA's. It is a process that brings data from a production database into a staging database where it's conditioned for Decision Support and OLAP.

The Business Intelligence dev team wanted the trigger to fire a completely seperate process that is used to push data to objects in a presentation database.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

I see. I think the DDL trigger can still do the job here (didn't check bol to be sure). I think it's your safest bet at this point.