Audit Table

Hi all gurus
I want to create a store procedure which will generate an audit table and
track all the changes done in any of my tables in any database of my
server.
Please guide me where to start
Any guidance will be highly appreciated
ThanksYou probably won;t want a stored proc, but a trigger on each of the tables
you want to audit.. I believe there's a MS design pattern you can copy foor
this. Check out MSDN... In general,
1) decide what you want to record.. . some ideas are
Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
ChangeDatetime, Operation(Insert/Update/Delete), etc...
2) build a table to store the data
3) write trigger in each table that inserts new record in audit table for
each row being modified...
hth,
Charles
"AM" wrote:

> Hi all gurus
> I want to create a store procedure which will generate an audit table and
> track all the changes done in any of my tables in any database of my
> server.
> Please guide me where to start
> Any guidance will be highly appreciated
> Thanks
>
>|||hi,
You can add to the strategic or central production's tables fields such as:
-LastDML ('u': update, 'i': insert)
-LastDMLuser (linked to tables as tblsysusers and tblsysrights)
"CBretana" wrote:
> You probably won;t want a stored proc, but a trigger on each of the tables
> you want to audit.. I believe there's a MS design pattern you can copy fo
or
> this. Check out MSDN... In general,
> 1) decide what you want to record.. . some ideas are
> Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> ChangeDatetime, Operation(Insert/Update/Delete), etc...
> 2) build a table to store the data
> 3) write trigger in each table that inserts new record in audit table for
> each row being modified...
> hth,
> Charles
> "AM" wrote:
>|||Hi all
I cannot change anykind on tables , so I can not add triggers on the tables
Thanks
AM
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> hi,
> You can add to the strategic or central production's tables fields such
as:
> -LastDML ('u': update, 'i': insert)
> -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
>
> "CBretana" wrote:
>
tables
foor
for
and|||Then you will need to intercept the Stored Proc(s) that are inserting,
updating, or deleting record sfrom these tables and put the same code, more
or less in there, understanding that any changes made to the tables
directly, or from another as yet unwritten SP< will not be audited...
"AM" wrote:

> Hi all
> I cannot change anykind on tables , so I can not add triggers on the table
s
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> as:
> tables
> foor
> for
> and
>
>|||This may be an option.
http://www.lumigent.com/
"AM" <anonymous@.extraquest.com> wrote in message
news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I cannot change anykind on tables , so I can not add triggers on the
tables
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> as:
> tables
copy
> foor
> for
table
> and
my
>|||Thanks
"Terri" <terri@.cybernets.com> wrote in message
news:d2s32a$tkr$1@.reader2.nmix.net...
> This may be an option.
> http://www.lumigent.com/
>
> "AM" <anonymous@.extraquest.com> wrote in message
> news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> tables
such
> copy
WHoChangedit,
table
> table
> my
>