5 Replies Latest reply: Mar 11, 2013 12:15 AM by ratnakar RSS

    how to perform audit?

    ratnakar
      can any one tell me...how to insert deleted or updated data into another table?
        • 1. Re: how to perform audit?
          Solomon Yakobson
          Trigger.

          SY.
          • 2. Re: how to perform audit?
            ratnakar
            if u don't mind could you tell me how its....?
            • 3. Re: how to perform audit?
              Hoek
              You can go for the cumbersome trigger road or use Oracle's AUDIT feature.
              See:
              http://www.oracle-base.com/articles/9i/complete-data-audit.php
              http://www.oracle-base.com/articles/10g/auditing-10gr2.php
              http://www.oracle.com/pls/db112/search?remark=quick_search&word=audit
              • 4. Re: how to perform audit?
                Solomon Yakobson
                ratnakar wrote:
                if u don't mind could you tell me how its....?
                SQL> create table tbl
                  2    as
                  3      select  * from dept
                  4  /
                
                Table created.
                
                SQL> select  *
                  2    from  tbl
                  3  /
                
                    DEPTNO DNAME          LOC
                ---------- -------------- -------------
                        10 ACCOUNTING     NEW YORK
                        20 RESEARCH       DALLAS
                        30 SALES          CHICAGO
                        40 OPERATIONS     BOSTON
                
                SQL> create table tbl_log(
                  2                       who varchar2(30),
                  3                       when date,
                  4                       what varchar2(1),
                  5                       old_deptno number,
                  6                       old_dname varchar2(14),
                  7                       old_loc varchar2(13),
                  8                       new_deptno number,
                  9                       new_dname varchar2(14),
                 10                       new_loc varchar2(13)
                 11                      )
                 12  /
                
                Table created.
                
                SQL> create or replace
                  2    trigger tbl_budr
                  3      before update
                  4          or delete
                  5      on tbl
                  6      for each row
                  7      declare
                  8          v_what varchar2(1);
                  9      begin
                 10          if updating
                 11            then
                 12              v_what := 'U';
                 13            else
                 14              v_what := 'D';
                 15          end if;
                 16          insert
                 17            into tbl_log
                 18            values(
                 19                   user,
                 20                   sysdate,
                 21                   v_what,
                 22                   :old.deptno,
                 23                   :old.dname,
                 24                   :old.loc,
                 25                   :new.deptno,
                 26                   :new.dname,
                 27                   :new.loc
                 28                  );
                 29  end;
                 30  /
                
                Trigger created.
                
                SQL> update  tbl
                  2     set  loc = 'ALBANY'
                  3    where deptno = 10
                  4  /
                
                1 row updated.
                
                SQL> delete tbl
                  2    where deptno = 30
                  3  /
                
                1 row deleted.
                
                SQL> select  *
                  2    from  tbl
                  3  /
                
                    DEPTNO DNAME          LOC
                ---------- -------------- -------------
                        10 ACCOUNTING     ALBANY
                        20 RESEARCH       DALLAS
                        40 OPERATIONS     BOSTON
                
                SQL> select  *
                  2    from  tbl_log
                  3  /
                
                WHO   WHEN      W OLD_DEPTNO OLD_DNAME  OLD_LOC  NEW_DEPTNO NEW_DNAME  NEW_LOC
                ----- --------- - ---------- ---------- -------- ---------- ---------- -------
                SCOTT 09-MAR-13 U         10 ACCOUNTING NEW YORK         10 ACCOUNTING ALBANY
                SCOTT 09-MAR-13 D         30 SALES      CHICAGO
                
                SQL>
                SY.
                • 5. Re: how to perform audit?
                  ratnakar
                  thank you so much...............