473,703 Members | 2,472 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

track all changes

hello,

I was wondering how to track all changes on tables by using some sort of a
history table.

What i would like is a generic history table where i can see who updated,
inserted, deleted or executed(stored procedures, triggers) what value in
what table with a date when it was occured.

Could somebody help me with this?
Jul 20 '05 #1
1 1624
EzekiŽl (ezekiŽl@lycos. com) writes:
I was wondering how to track all changes on tables by using some sort of a
history table.

What i would like is a generic history table where i can see who updated,
inserted, deleted or executed(stored procedures, triggers) what value in
what table with a date when it was occured.

Could somebody help me with this?


It's a tall order, and you could easily spend too much time on this. If
you are looking for a full-scale solution, I strongly advice you to look
at third-party products. The two I usually quote are Lumigent's Entegra
and Red Matrix' SQL Audit. Lumigent works from the transaction log which
is quite a palatable solution. SQL Audit uses triggers. I have no personal
experience of either products.

But if you are prepared to lower your level of ambition, here is table
that I designed recently:

CREATE TABLE basiclogs2 (
bl2id int IDENTITY,
tablename sysname NOT NULL,
keyvalue1 sql_variant NOT NULL,
keyvalue2 sql_variant NULL,
appname sysname NULL
CONSTRAINT default_bl2_app name DEFAULT app_name(),
hostname sysname NULL
CONSTRAINT default_bl2_hos tname DEFAULT host_name(),
moddate datetime NOT NULL
CONSTRAINT default_bl2_mod date DEFAULT getdate(),
moduser sysname NOT NULL,
CONSTRAINT default_bl2_mod user DEFAULT SYSTEM_USER,
CONSTRAINT pk_bl2 PRIMARY KEY NONCLUSTERED (bl2id))

Then in the tables I am interested to track, I have added code to insert
data into that table like:

INSERT basiclogs2 (tablename, keyvalue1, keyvalue2)
SELECT 'thistable', keycol1, keycol2
FROM inserted

That is, I am only logging that a certain row has been changed, but not
what. (We do have a table for this as well, but it logs selected columns.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
4759
by: LC | last post by:
hi, i worry about people doing something they shouldn't to my db and I would like to track any structural changes (who and which)to my db. I am using oracle 8.0.6.0.0 and 9.2.0.2.0. regards, Lee Cho
1
2841
by: JLuppens | last post by:
Does anyone know if there is a way to track changes in a text field like word does? I am using a SQL2000 database and can use either VB.Net or Access. The field is either ntext or Varchar. I would like to show users the changes other users have made to a text field using a other font or font color. Thanks
4
3187
by: SQLDBA | last post by:
What would be the best practice to follow to keep track of MS SQL server changes... Stroed procs, tables, views, triggers, indexes, DTS and also jobs ect.... I am not quite sure how Source safe works with sql server. Any other way to do this... Even if its manual work, its okey.. I would appreciate if any of the DBA's let me know how they are facing this issue.... Thanks in advance...
4
4506
by: Nicole | last post by:
Hello: I have a shared database running on a network drive. I would like to track when record changes occur and who did it. Problem 1: I'm not able to tack changes, when they occur in subforms. (I have a main form with 3 subforms) Problem 2: for CurrentUser I'm currently getting "admin", since I don't have any security levels set up, however everybody in our system
2
4175
by: Uwe Range | last post by:
Hi to all, I am developing a database where, with time, it proved to be necessary to keep track of the changes to the data. It is fairly easy to keep track of the time when a record was changed (afterupdate event) -> Is there there a smart way of finding out which field has been changed? I don't want to set a procedure on every field on the edit form for keeping track of that, because the forms will be changed from time to time.
5
4035
by: Daniel Walzenbach | last post by:
Hi, I need to track all changes made to an object. Consider the following class: Public Class Dog
2
2767
by: metaperl | last post by:
I'm actually taking Microsoft's 2779 and just finished a lab where we kept track of our changes to the database. However, I'm not happy with the scripts interface because it does not tell me the chronological order of my changes to the database. Could someone share with me their technique for keeping track of database changes? I'm actually thinking a set of tables would be best, because sometimes
3
2600
by: PJ6 | last post by:
What's the best way to, in the general case, track changes to an object's public fields and properties? In a lot of applicatoins I've seen bahavior consistant with simple periodic checking from a timer, or checking otherwise initiated from a user event. I don't believe I can make that approach work in the project I'm working on... are there other ways to do this, where I can actually get an event raised on a field change, that don't involve...
20
3138
by: John Sheppard | last post by:
Hello there, Does anyone know of a way to track changes to an SQL Server database so that I can easily run those changes at a later date? That is, I want to make schema changes, and record those changes so that I can execute them 6 months later on a copy of the orignal database. Thank you kindly for any ideas anyone may have John
3
4190
by: dirksza2009 | last post by:
Hi, I've made a multi user (4 end users) database in Access 2000. I've made data tables, reference tables etc which sits on a shared drive and I've made individual front ends for the end users which gives them specific views of the data. I'd like to track all the changes made to a record which works find with the following code : basAuditTrail: Option Compare Database Const cDQ As String = """" Sub AuditTrail(frm As Form, recordid As...
0
8672
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9123
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8969
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7876
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectóplanning, coding, testing, and deploymentówithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6594
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4434
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4689
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2462
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2070
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.