Capture Changes

[Ken]

Is there a way to capture every change made in a database? I would like to be able to audit and report on all changes made in our corp. database. Is there a system tool or function that can accomodate such a thing? Transaction Log maybe??

[MAK]

1. SQL Profiler
2. Lumingent Log Explorer (http://www.lumigent.com/)
For snapshots - http://www.mssqlserver.com/products/logexplorer20_p1.asp



------------
Ken at 5/23/2002 6:13:18 PM

Is there a way to capture every change made in a database? I would like to be able to audit and report on all changes made in our corp. database. Is there a system tool or function that can accomodate such a thing? Transaction Log maybe??

[Ken]

Thanks MAK. Both of these tools seem very powerful for a DBA. Unfortunately, the end user here is not a DBA. She is a our Data manager but not an administrator. I was looking for something that would be more user friendly for her. What about using a trigger to capture changes to certain tables? Would this be a sound approach or is it too much overhead.

Ken


------------
MAK at 5/24/2002 8:33:48 AM

1. SQL Profiler
2. Lumingent Log Explorer (http://www.lumigent.com/)
For snapshots - http://www.mssqlserver.com/products/logexplorer20_p1.asp



------------
Ken at 5/23/2002 6:13:18 PM

Is there a way to capture every change made in a database? I would like to be able to audit and report on all changes made in our corp. database. Is there a system tool or function that can accomodate such a thing? Transaction Log maybe??

[MAk]

Trigger can only keep track of update delete and insert. What will happen if there is a new table, or a table has been dropped or something. Moreover if u want to track all the three kind of changes u need to have 3 different tables or one table to store all the transactions in that table. it comes as over loading. to much IO operations.



If u want to capture every details of the database u can use sql profiler.


Eg: for trigger that simulate the Transactional log for a particular table

create table OriginalTable (id int, name char(10))

create table OriginalTable_Log (type char(1),id int, name char(10))

create trigger OriginalTable_Insert on OriginalTable for
Insert as
insert into OriginalTable_Log (type,id,name) select "I",id,name from inserted

create trigger OriginalTable_Update on OriginalTable for
Update as
insert into OriginalTable_Log (type,id,name) select "U",id,name from Deleted
insert into OriginalTable_Log (type,id,name) select "U",id,name from inserted

create trigger OriginalTable_Delete on OriginalTable for
Delete as
insert into OriginalTable_Log (type,id,name) select "D",id,name from Deleted


insert into OriginalTable select 1,"MAK"
insert into OriginalTable select 2,"MAK"
insert into OriginalTable select 3,"MAK"
insert into OriginalTable select 4,"MAK"
insert into OriginalTable select 5,"MAK"

update OriginalTable set name = "Smith" where id = 5


delete from OriginalTable where id = 1

select * from OriginalTable_log

Result:
type id name
---- ----------- ----------
I 1 MAK
I 2 MAK
I 3 MAK
I 4 MAK
I 5 MAK
U 5 MAK
U 5 Smith
D 1 MAK


------------
Ken at 5/24/2002 9:09:24 AM

Thanks MAK. Both of these tools seem very powerful for a DBA. Unfortunately, the end user here is not a DBA. She is a our Data manager but not an administrator. I was looking for something that would be more user friendly for her. What about using a trigger to capture changes to certain tables? Would this be a sound approach or is it too much overhead.

Ken


------------
MAK at 5/24/2002 8:33:48 AM

1. SQL Profiler
2. Lumingent Log Explorer (http://www.lumigent.com/)
For snapshots - http://www.mssqlserver.com/products/logexplorer20_p1.asp



------------
Ken at 5/23/2002 6:13:18 PM

Is there a way to capture every change made in a database? I would like to be able to audit and report on all changes made in our corp. database. Is there a system tool or function that can accomodate such a thing? Transaction Log maybe??