Sunday, March 11, 2012

Auditing changes to data

Hi Experts,
I want to audit the changes made to the data in the SQL
Server tables. How can I implement a audit mechanism?
Thanks,
HariHi,
You will have to use either of below 2 options:-
1. Write Insert / Update and delete triggers on each tables
2. Enable profiler, You have to run the profiler all the time.
But I feel that your pupose can only be solved by writing custom triggers on
each tables.
Thanks
Hari
MCDBA
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:278e01c47dd7$09469dc0$a401280a@.phx.gbl...
> Hi Experts,
> I want to audit the changes made to the data in the SQL
> Server tables. How can I implement a audit mechanism?
> Thanks,
> Hari|||Hari
Create Audit Table with the same structure as real table . Create an
UPDATE/DELETE/INSERT trigger on real table and insert any changes into audit
table.
create trigger trigger_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:278e01c47dd7$09469dc0$a401280a@.phx.gbl...
> Hi Experts,
> I want to audit the changes made to the data in the SQL
> Server tables. How can I implement a audit mechanism?
> Thanks,
> Hari|||There are also third party tools which audit by reading the transaction
logs... Lumigent has such a tool (www.Lumigent.com.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:278e01c47dd7$09469dc0$a401280a@.phx.gbl...
> Hi Experts,
> I want to audit the changes made to the data in the SQL
> Server tables. How can I implement a audit mechanism?
> Thanks,
> Hari

No comments:

Post a Comment