Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    PA
    Posts
    9

    Exclamation Unanswered: HELP please- Trigger

    I nee to create a trigger, so that when the base table is changed, the entire row is copied to the shadow (Audit) table.

    Anyone have any code handy for this in Oracle?

    Thanks in advance.

    mzimm

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Talking Trigger code

    Hello,

    let us say "basetab" is the base table and "audittab" is the audit table
    where bastab contains

    1. id NUMBER
    2 name VARCHAR2
    3 joindate DATE

    and audittab contains

    1. id_old NUMBER
    2. id_new NUMBER
    3. name_old VARCHAR2
    4. name_new VARCHAR2
    5. joindate_old DATE
    6. joindate_new DATE
    7. change_date DATE

    User scott is the owner of both objects ...

    OK ?

    First connect as scott ...


    Second create a trigger on the basetable that fires when a record has been inserted. In this trigger you will insert the datas into your audit table.

    CREATE OR REPLACE TRIGGER scott.ari_audit AFTER INSERT,
    UPDATE, DELETE ON
    scott.basetab REFERENCING OLD AS old NEW AS new
    FOR EACH ROW
    BEGIN
    INSERT INTO scott.audittab VALUES
    (ld.id, :new.id, ld.name, :new.name, ld.joindate, :new.joindate,
    SYSDATE);
    END;


    Thats all ...
    If you want to get information about the user who has changed the record then use the DBMS_STANDARD package to get the username
    and put this into your audittable.

    username := dbms_standard.login_user;


    If you would like to create triggers with a gui dialog the use AlligatorSQL
    at http://www.alligatorsql.com/download/alligator.zip

    I hope that helps ?

    Regards

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Re: smilies

    ld.id means

    : o l d . i d

    Ok ?

    Regards

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com
    Last edited by alligatorsql.com; 10-09-02 at 07:31.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •