Update table data on server restart...

Question

Update table data on server restart...

davidc-702140

Say Hey Kid

Points: 665
More actions
April 8, 2008 at 4:51 am

#186129

Hi,
I'm new to sql to please excuse me if some of this seems obvious.
I'm using SQL server 2005 and I want to be able to update some data in a table whenever the server is re-started. My first thought was to use DLL triggers on the server. I know that the tempdb is re-created everytime the server is restarted, so I though I could use a trigger with a CREATE_DATABASE event on the server. However, although this is what I've done, when I restart the server, it doesn't seem to fire the trigger.
I'm guessing it is a common problem, to re-set data in a table whenever a server is restarted. Yet I can't see any obvious way other then detecting when tempdb is re-created and using a trigger. However, this doesn't seem to work. Can anybody please help???
Here is the trigger I've got..................
create trigger [InitializeData]
on all server
for create_database
as
set nocount on
update MyDatabase.dbo.MyTable SET MyColumn = 'Initialized'

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

I'm curious. Why do you need to do this?

You can use a startup procedure, or you can create a job that will run when SQL agent starts. Either will probably work.

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

davidc-702140 Say Hey Kid Points: 665 More actions · Answer 2

Hi Gail,

Thanks for your reply. The database manages licenses that get handed out when requested by a client. The application renews its license by polling the database every few minutes. If the server goes down, the app can't contact the server and will force the user to quit the application. Here is the problem. When the server restarts one of my columns indicates whether a license is available or not, obviously if the server is restarted, ALL the licenses will be available, so I need to update all the data in that column. Hope that makes sense.....

Thanks for the tips, I'm not familiar with either of them, so I'll go away and have a read up on them.......

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Create a procedure to mark the licenses as available and then mark it as a "startup procedure" as Gail suggested.

davidc-702140 Say Hey Kid Points: 665 More actions · Answer 4

Thanks Gail / Steve,

I've used a stored procedure to run on start up and it's doing exactly what I want!

Thanks again for your help - much appreciated!