Re: Triggers

From: <david.archer_at_chase.com>
Date: Mon, 30 Aug 1999 15:48:04 GMT
Message-ID: <7qe93g$s29$1@nnrp1.deja.com>

First let me give you a few assumptions I have made. 1. All tables in my schema have one and only one field for a primary key. This primary key is always VARCHAR2(20) or less. I prefer to used sequence generators to create keys which makes it uniform for all tables.

2. I do not audit LONG fields.
3. I do not audit values for fields over 100 characters in length.
4. I know there's more but I can't remember off the top of my head.

Here is the structure of my Audit Table.

Name

PRIMARY_KEY       - Set by a sequence genertor is PK of Audit table.
KEY_VALUE         - PK of record being changed.
TABLE_NAME        - Table updated.
COLUMN_NAME       - Column updated.
OLD_VALUE         - old value of field being updated.
NEW_VALUE         - new value of field being updated.
CHANGED_DATE      - date value changed (DEFAULT SYSDATE)
CHANGED_BY        - who changed it (DEFAULT USER)
ACTION            - UPDATE, DELETE OR INSERT

Implementation example:
Table1 has three fields pk (primary_key), field1, field2 DATE

IF :NEW.field1 <> :OLD.field1 THEN
  INSERT INTO Audit_Changes
    (key_value, table_name, column_name, old_value, new_value, action)   VALUES
    (:NEW.pk_field, 'TABLE1', 'FIELD1', :OLD.field1, :NEW.field1,

       'UPDATE');
END IF; IF :NEW.field2 <> :OLD.field2 THEN
  INSERT INTO Audit_Changes
    (key_value, table_name, column_name, old_value, new_value, action)   VALUES
    (:NEW.pk_field, 'TABLE1', 'FIELD2',

        TO_CHAR(:OLD.field1,'YYYY-MM-DD HH24:MI:SS'),
        TO_CHAR(:NEW.field1,'YYYY-MM-DD HH24:MI:SS',
        'UPDATE');

END IF; Obviously this type of code gets very very very tedious! I wrote some code which will write this code for me. Here is where the common key really paid off! Since all tables only have one primary key and it was always VARCHAR2(20) (actually some are less) then the code generator was pretty easy to implement.

Happy Coding,

David Archer

In article <37C87FCF.298504D2_at_mindspring.com>, "William F. O'Neill" <wfoneill_at_mindspring.com> wrote:
> Can someone supply me with an example of a Trigger as used on an
Oracle
> database, eg. a trigger that would update an audit table, depending on
> whether the action on a table was Update, Delete, or Insert. To
clarify,
> if I edit Table_A, I want the trigger to activate, bring up the Audit
> table, and indicate in an Action field that Table_A was just Updated,
> and that a particular field's value changed ->new value, and what the
> old value was. If you have any code that is in anyway similar to my
> requirements, I'd appreciate it. thank you.
> Bill....
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Aug 30 1999 - 10:48:04 CDT