Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: Trigger Insert Update Delete ?

    Hello, I am not familiar with Oracle at all, but I find myself in a situation where I need to build a update trigger for one of our tables. This trigger needs to update a tracking table anytime any of the fields get updated.

    Thanks
    Last edited by abliss; 11-19-10 at 02:53.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example (based on Scott's schema) of how you might do that.

    First, create a LOG table (which will contain changes made on the "original" DEPT table). "WHERE 1 = 2" means: create a table with all columns as the original one, but make it empty (because no records satisfy "1 = 2" condition). Add two more columns: action (I - insert, U - update, D - delete) and datum (timestamp which will tell us when something happened).
    Code:
    SQL> create table dept_log as
      2    select deptno, dname, loc
      3    from dept
      4    where 1 = 2;
    
    Table created.
    
    SQL> alter table dept_log add datum date;
    
    Table altered.
    
    SQL> alter table dept_log add action varchar2(1);
    
    Table altered.
    
    SQL>
    Now, let's create a trigger. Note use of :NEW and :OLD values.
    Code:
    SQL> create or replace trigger trg_dept
      2    before insert or update or delete on dept
      3    for each row
      4  begin
      5    if inserting then
      6       insert into dept_log (action, datum, deptno, dname, loc)
      7         values
      8         ('I', sysdate, :new.deptno, :new.dname, :new.loc);
      9    elsif updating then
     10       insert into dept_log (action, datum, deptno, dname, loc)
     11         values
     12         ('U', sysdate, :old.deptno, :old.dname, :old.loc);
     13    elsif deleting then
     14       insert into dept_log (action, datum, deptno, dname, loc)
     15         values
     16         ('D', sysdate, :old.deptno, :old.dname, :old.loc);
     17    end if;
     18  end;
     19  /
    
    Trigger created.
    
    SQL>
    OK, let's do some testing:
    Code:
    SQL> insert into dept (deptno, dname, loc) values (99, 'test', 'test loc');
    
    1 row created.
    
    SQL> update dept set dname = 'my test' where deptno = 99;
    
    1 row updated.
    
    SQL> delete from dept where deptno = 99;
    
    1 row deleted.
    
    SQL> select action, datum, deptno, dname, loc
      2  from dept_log
      3  order by datum;
    
    A DATUM                   DEPTNO DNAME          LOC
    - ------------------- ---------- -------------- -------------
    I 17.11.2010 08:20:19         99 test           test loc
    U 17.11.2010 08:20:33         99 test           test loc
    D 17.11.2010 08:20:48         99 my test        test loc
    
    SQL>
    More reading is available in CREATE TRIGGER section of Oracle documentation. Perhaps you should also take a look at Coding Triggers section of the Application Developer's Guide - Fundamentals book.

Posting Permissions

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