Update/Insert based on insert on another table

[anniez]

I have three tables (A,B & C). C gets refreshed every 15 minutes.
If a new record is added, both A & B will be effected. If an IDcode isn't in those files it must insert a new record - it will have several columns in each table that would be completed. If the IDcode is already there, it updates those columns.

Can you get me started with a procedure? a trigger? the code? I'm not sure where to start.
THANKS

 

[srishan]

-- create a new table with same structure as emp
SQL> create table emp2 as select * from emp where 1 = 2;

Table created.

-- create a trigger on new table
SQL> create or replace trigger emp2_tgr1
  2  before insert on emp2
  3  for each row
  4  begin
  5    update emp set ename = :new.ename, deptno = :new.deptno
  6    where  empno = :new.empno;
  7    if sql%rowcount = 0 then
  8       insert into emp(empno, ename, deptno) values(:new.empno, :new.ename, :new.deptno);
  9    end if;
 10  end;
 11  /

Trigger created.

-- let us look at MILLER record in EMP table
SQL> select empno, ename, deptno from emp where empno = 7934;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7934 MILLER             10

-- now, let us try to insert a record in NEW table with
-- same empno as MILLER
SQL> insert into emp2(empno, ename, deptno) values(7934, 'BOB', 20);

1 row created.

-- now let us look at MILLER in EMP table
SQL> select empno, ename, deptno from emp where empno = 7934;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7934 BOB                20

-- let us look at EMP table for empno 1111
SQL> select empno, ename, deptno from emp where empno = 1111;

no rows selected
 
-- let us insert 1111 into NEW table
SQL> insert into emp2(empno, ename, deptno) values(1111, 'SRI', 20);

1 row created.

-- let us look at EMP table for 1111 again
SQL> select empno, ename, deptno from emp where empno = 1111;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      1111 SRI                20

-- let us look at NEW table for all rows
SQL> select empno, ename, deptno from emp2;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7934 BOB                20
      1111 SRI                20

This should be a start. You need trigger like this.
I have shown the dml on one table, you can add dml to the second table. Also you should expand the trigger to include error handling. Please read about triggers.

HTH,
Sri