www .Just SQL. com  

Script Name :

Description :

a site for the SQL*Plus specialist

Site Map

TABLE AUDITING with Automated Trigger Construct

Six scripts controlling the persistent storage of all pre-change modifications made to tables identified to be Audited.

This section presents a suite of six scripts, four of which make use of the code generation function being demonstrated on this site.

     The Objective is to build a system which
          a) saves every pre-modified version of rows from a table each time it is subject to modification, and
          b) logs the instance of every modification to a specified table.

     Each time the specified table is modified, two things will happen.

     A record of the Table_Name, the Date, and the Activity (whether Insert, Update or Delete) will be recorded in the logging table  LOG_MODS.

     When an existing row is updated or deleted, the pre-modified row will be copied to the relevant Copy (or audit) table (a mirror image of the original specified table), with flags indicating the name of the person making the change, the date, and a flag to show whether the row was Updated or Deleted.

     The system requires several new objects, which are created by the scripts below. Example names assume a specified table called  'MY_TABLE':

Download each script from the above, or the full suite in zip format here: Master_audit.zip

[Home] [Inspiration] [About this Site] [Scripts Intro] [Publications]

e-Mail Connection