This:
Quote:
Originally Posted by m_tubati
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.