Monitoring changes on DB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Monitoring changes on DB

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-08 : 08:00:44

Dion writes "Hi,
I need to monitor updates made to tables in a database. What I have done is insert a column programatically into each table in the database and have this value set on insert. Updates is another problem. These changes to the db are made from outside of my app. I am considering extracting the update script programatically(not exactly sure how) and insert an instruction to flag the updflag column. Is there an easier way of doing this? Are there no temp tables I can parse to get the tables and rows affected?

Many thanks,
Dion.

SQL 6.5 Running NT on the server
98 on client"

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-08 : 08:23:27

You could use a trigger. Look at "CREATE TRIGGER" in books online.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-08 : 11:47:37

quote:

SQL 6.5 Running NT on the server
98 on client

[shudder]tingly feeling down my spine[/shudder]


CREATE TRIGGER <yourTable>_UpdDelTr ON Company
FOR INSERT, UPDATE, DELETE 
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
	BEGIN
           ...updated code
        END 
If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
	BEGIN
           ...Delete code
        END 
If Exists (Select 1 From Inserted) And Not Exists (Select 1 From Deleted)
	BEGIN
           ...Insert code
        END 
GO

[BlueScreenOfDeath]GOOD LUCK[/BlueScreenOfDeath]

Brett

8-)

Edited by - x002548 on 05/08/2003 11:48:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources