By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,610 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,610 IT Pros & Developers. It's quick & easy.

System event triggers

P: n/a
Hi, I have several independent, system components that write audit
events to a database locally via ODBC and remotely via TCP, and I have
a requirement to audit when the database is available for modification.
First, does SQL Server have a concept of starting/stopping specific
database instances? Or does my database instance "stop" only when the
NT service MSSQLSERVER stops?

Second, Oracle has system event triggers, e.g. AFTER STARTUP, but I
can't find an equivalent in SQL Server 2000. What alternatives are
there?

Note that I can't rely on the MSSQLSERVER start/stop events in the NT
Application Event log, as the events need to be inserted "in-band" into
my database's event table.

TIA,
Josh

Jul 24 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
you can take a database offline or make it single user only

USE master
GO
ALTER DATABASE pubs
SET OFFLINE--use ONLINE to make it available again

>>Oracle has system event triggers, e.g. AFTER STARTUP, but I
can't find an equivalent in SQL Server 2000.
Well you can create a procedure and have it execute every time the SQL
Server is restarted
The procedure has to be created in the master database, after it is
created you have to use sp_procoption to have the procedure execute
when SQL Server starts up

--Let's create our procedure
USE master
GO
CREATE PROCEDURE prStartUp
AS
SELECT GETDATE()
--You would do something real here
--like deleting the data
GO
--Make the procedure execute when the server starts up
sp_procoption prStartUp,startup,'on'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
josh wrote:
Hi, I have several independent, system components that write audit
events to a database locally via ODBC and remotely via TCP, and I have
a requirement to audit when the database is available for modification.
First, does SQL Server have a concept of starting/stopping specific
database instances? Or does my database instance "stop" only when the
NT service MSSQLSERVER stops?

Second, Oracle has system event triggers, e.g. AFTER STARTUP, but I
can't find an equivalent in SQL Server 2000. What alternatives are
there?

Note that I can't rely on the MSSQLSERVER start/stop events in the NT
Application Event log, as the events need to be inserted "in-band" into
my database's event table.

TIA,
Josh
Jul 24 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.