How to create a table for auditing?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to create a table for auditing?

  1. #1
    Join Date
    Aug 2000
    Posts
    11
    Hi,

    I want to create a table for auditing, i.e. a table where each database transaction is stored with user name and date. Example: I have the following tables:

    create table demo1(
    Name Varchar2(50));

    create table audittable(
    transaction varchar2(100),
    t_user varchar2(20),
    t_date Date);

    create or replace trigger audittrigger
    after insert or delete or update on demo1
    for each row
    begin
    insert into audittable values(????, user, sysdate);
    end;
    /

    Whenever a user inserts or deletes or updates data in the table demo1, I want to log the changings in the table audittable. It is no problem to store the user name and the date, but how can I get the transaction, e.g. "'insert into demo1 values('Dan');" ? I want the audittable like this:

    Transaction | User | Date
    -------------------------------------------------
    Insert into demo1 values ('Dan') | Scott | 27-OCT-00

    Thx in advance!
    Dan

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    create or replace trigger audittrigger
    after insert or delete or update on demo1
    for each row
    begin
    if inserting
    insert into audittable values('INSERT', user, sysdate);
    elsif updating
    insert into audittable values('UPDATE', user, sysdate);
    elsif
    insert into audittable values('DELETE', user, sysdate);
    end if;
    end;
    /

  3. #3
    Join Date
    Aug 2000
    Posts
    11

    Smile

    Thx for your reply!
    Dan

Posting Permissions

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


Click Here to Expand Forum to Full Width