#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11

    Oracle Trigger :new/:old row


    Hey all,

    Just been playing around with triggers and :new/: old and I have about 10 tables that I want to generate some triggers for to audit any inserts/updates/deletes - so far so good. I can access all the data, timestamp any changes and use :new.col# / : old.col# to populate what data changes.

    But...what I'd like to do is store :new or : old as an entire entity...so when I insert the changed data into a new table I want to just insert :new or : old as they are (will all the row data) in a single column rather than have to code each trigger to insert data into a audit table designed for each trigger table. I'd like multiple triggers to all update a single audit table with 1 column containing whatever :new or : old comprises of...

    Is this possible or do I have to specify a column name with :new and : old ?

    If not, can I pass :new or : old to a function to loop through and build my own single string ?

    Cheers

    Ben
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    840
    Rep Power
    387

    Cool


    Originally Posted by amstel_za
    ... Etc ... I'd like multiple triggers to all update a single audit table with 1 column containing whatever :new or : old comprises of...
    You could start by designing the audit table, something like this:
    Code:
    -- audit_table
    -- Column            Type            Description
    ----------------- --------------- -------------------------
     PK_COLUMNS       VARCHAR2(200)   Primary key column names
     PK_VALUES        VARCHAR2(200)   Primary key value
     TABLE_NAME       VARCHAR2(32)    Audited table name
     COLUMN_NAME      VARCHAR2(32)    Changed column name
     VALUE_FROM       VARCHAR2(4000)  Old value
     VALUE_TO         VARCHAR2(4000)  New value
     USER_ID          VARCHAR2(100)   Changed by
     DATE_TIME        DATE            Change timestamp

    PS: Forget about the "1 column containing" thing, keep it short and simple (KISS).
    Last edited by LKBrwn_DBA; May 3rd, 2012 at 12:23 PM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    Originally Posted by LKBrwn_DBA
    You could start by designing the audit table, something like this:
    Code:
    -- audit_table
    -- Column            Type            Description
    ----------------- --------------- -------------------------
     PK_COLUMNS       VARCHAR2(200)   Primary key column names
     PK_VALUES        VARCHAR2(200)   Primary key value
     TABLE_NAME       VARCHAR2(32)    Audited table name
     COLUMN_NAME      VARCHAR2(32)    Changed column name
     VALUE_FROM       VARCHAR2(4000)  Old value
     VALUE_TO         VARCHAR2(4000)  New value
     USER_ID          VARCHAR2(100)   Changed by
     DATE_TIME        DATE            Change timestamp

    PS: Forget about the "1 column containing" thing, keep it short and simple (KISS).
    Many thanx LKBrwn_DBA - appreciate the quick response. I do have an audit table but...my biggest issue is that if (when) someone alters a certain table, they may change a number of columns worth of data so I won't quite know what to enter as VALUE_FROM/VALUE_TO - so as a compromise I just wanted to say: "whatever column is being altered / added / deleted - just insert that entire row (and all columns) into a single varchar2(n)
    column...

    Does that make more sense (I sense I've been a bit useless in explaining what I'm after) ?

    At the moment I have a unique Audit table for each table I have a trigger on (7 of them) so I'm just thinking of having 1 audit table. No-one other than me will have access to it and I know the how to interpret the data that will be inserted so even if the whole row is inserted it won't add any complexity to debugging.

    Cheers
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I agree with the suggestion you were given with perhaps a tweak (or just additional explanation)

    Code:
    -- audit_table
    -- Column            Type            Description
    ----------------- --------------- -------------------------
    ID                     INTEGER           Primary Key 
    DATE_TIME        TIMESTAMP       Change timestamp
    TABLE_NAME       VARCHAR2(32)    Audited table name
    PK_COLUMNS       VARCHAR2(200)   Primary key column names
    PK_VALUES         VARCHAR2(200)   Primary key value
    COLUMN_NAME    VARCHAR2(32)    Changed column name
    VALUE_FROM       VARCHAR2(4000)  Old value
    VALUE_TO           VARCHAR2(4000)  New value
    USER_ID             VARCHAR2(100)   Changed by
    One row in the table per changed column.
    DATE_TIME is captured in the trigger so that all changes generated by a single SQL
    statement can be captured by querying the audit table by filtering on TABLE_NAME,DATE_TIME

    Also, if you need it, you might want a column in the table to signify the type of SQL that generated the activity.

    Code:
    SQL_ACTION    VARCHAR2(1)  --(I)nsert,(D)elete,(U)pdate
    Clive
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    Ok excellent, that would work equally (scratch that, even better)

    Each row is a column that is changed within a table rather than 1 row for all changes, much neater! Many thanx.

    In my original table I have included the type of change (Insert,Delete,Update) as well - including previous/post change values. The table is a configuration table which shouldn't be edited but I've found anomalies in the past so just putting together a decent audit so that if anything goes wrong I'll have a trail of changes made and by whom.

    Many thanx! Row per column change is a much better idea. Appreciate the advice.

    Ben

IMN logo majestic logo threadwatch logo seochat tools logo