Using Triggers for Table Audit 
Author Message
 Using Triggers for Table Audit

We are in the process of designing large Sybase database(5-10 GB) with
updates from 100+ concurrent users.  The database will have approx. 300
tables, out of which 50 tables need to be audited(storing before image) for
reporting.  We would like to know the performance(or other) implications of
using Triggers to update Audit tables.

If a Table has 20 columns and user can directly change only 12 columns and
others are derived columns, the Audit should be done only if one of the 12
columns is changed.  To implement this in a Trigger, we would check for
these 12 columns as being Updated and conditionally create an audit.  What
are the performance and maintenance implications of these long and
complicated Triggers.

Sharing any real world experience is appreciated. I will post a summary, if
I get good response.  Thanks.



Tue, 26 Dec 1995 00:49:50 GMT
 Using Triggers for Table Audit

Quote:

>We are in the process of designing large Sybase database(5-10 GB) with
>updates from 100+ concurrent users.  The database will have approx. 300
>tables, out of which 50 tables need to be audited(storing before image) for
>reporting.  We would like to know the performance(or other) implications of
>using Triggers to update Audit tables.

>If a Table has 20 columns and user can directly change only 12 columns and
>others are derived columns, the Audit should be done only if one of the 12
>columns is changed.  To implement this in a Trigger, we would check for
>these 12 columns as being Updated and conditionally create an audit.  What
>are the performance and maintenance implications of these long and
>complicated Triggers.

>Sharing any real world experience is appreciated. I will post a summary, if
>I get good response.  Thanks.

I have used these with no substantial problems.  I do not have figures
on updates/second before and after.  Also if the remaining columns are
derived then an update to the derived columns implies an update to
the 12 primary columns therefore there is no need to check the individual
fields.  This should make the trigger less complicated unless the derived
values are maintained separately from the primary data.

Also what audit record is stored for an insert?  There is no before image
to store.  If you store the after image including the original insert then
the data can be reconstructed to any point in time.



Fri, 29 Dec 1995 22:01:52 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Creating Audit Trail using triggers with 100 tables.

2. Auditing using Triggers

3. update trigger used for audit

4. audit options without using triggers

5. Auditing table changes with triggers

6. Trigger to create audit table

7. Triggers for Audit Table

8. Dynamic Sql - Trigger / Audit Tables

9. auditing with triggers on system tables

10. Table data auditing / triggers

11. trigger for db audit purposes - newbie to the world of triggers


 
Powered by phpBB® Forum Software