Saturday, February 25, 2012

Audit Design Question


I need to audit Insert, Update and Delete on tables in the database.
But the symin of the app can selectively enable and disable auditing
on tables. So I need to be able to switch the auditing on and off.
Is there any built-in function in SqlServer 2005 that I can use to
track changes?
In the absence of built-in auditing I have come up with two possible
solutions:
1) Pass a patameter called @.bAudit to the stored procedures performing
CRUD tasks and if the parameter is true then insert a row into the
audit table (audit table has the same schema as the main table but with
a few more columns for tracking).
2) Use triggers on Insert, Update and Delete but I can't find out how
to disable triggers at run time.
Can you please point me in the right direction? Thanks.S Chapman,

> 2) Use triggers on Insert, Update and Delete but I can't find out how
> to disable triggers at run time.
Use the statement "alter table".
alter table dbo.t1
disable trigger trigger_name
See BOL for more info.

> 1) Pass a patameter called @.bAudit to the stored procedures performing
> CRUD tasks and if the parameter is true then insert a row into the
> audit table (audit table has the same schema as the main table but with
> a few more columns for tracking).
A possible solution could be having an options table and check the option
inside the trigger.
update conf_options
set audit = 1
where table_name = 'my_table'
go
create trigger tr_mytable on my_table
for insert, update, delete
as
if exists (select * from dbo.conf_options where table_name = 'my_table' and
audit = 1)
begin
-- put here the audit code
end
go
AMB
"S Chapman" wrote:

>
> I need to audit Insert, Update and Delete on tables in the database.
> But the symin of the app can selectively enable and disable auditing
> on tables. So I need to be able to switch the auditing on and off.
> Is there any built-in function in SqlServer 2005 that I can use to
> track changes?
> In the absence of built-in auditing I have come up with two possible
> solutions:
> 1) Pass a patameter called @.bAudit to the stored procedures performing
> CRUD tasks and if the parameter is true then insert a row into the
> audit table (audit table has the same schema as the main table but with
> a few more columns for tracking).
> 2) Use triggers on Insert, Update and Delete but I can't find out how
> to disable triggers at run time.
> Can you please point me in the right direction? Thanks.
>

No comments:

Post a Comment