View Single Post
  #3 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,980
This:
Quote:
Originally Posted by m_tubati View Post
We need to insert into master table whenever there is a change in any column data in stage table.
looks like a database trigger would do the job. Here's an example: first, create an empty "master" table (the original Scott's DEPT table is your "stage" table, then):
Code:
SQL> create table my_dept as select * from dept where 1 = 2;

Table created.

SQL> create or replace trigger trg_bu_dept
  2    before update on dept
  3    for each row
  4  begin
  5    insert into my_dept
  6      (deptno, dname, loc)
  7      values
  8     (:old.deptno, :old.dname, :old.loc);
  9  end;
 10  /

Trigger created.
Let's try it:
Code:
SQL> select * from my_dept;

no rows selected

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> update dept set dname = 'sales' where deptno = 30;

1 row updated.

SQL> select * from my_dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 sales          CHICAGO
        40 OPERATIONS     BOSTON

SQL>
As of "duplicate data", I'm as puzzled as much as Anacedent is.
Reply With Quote