After Update PL/SQL Trigger between two Users

I have a Table A in Schema A and Table B in Schema B.

Schema B.Table B has Product Information taken from Schema A.Table A ( this is the main开发者_C百科 database for Products Profile).

When ever Update happens to products Information in Schema A.Table A, that Update should reflect in Schema B. Table B ?

How can i write trigger for it ?..

I have ProductID in both tables

---

Why not create a before update trigger? The inserts to tableB will only commit if the entire transaction commits.

EDIT: if you want updates to tableB, try this:

--drop table testtab_a;

create table testtab_a
(
col1 varchar2(10) primary key,
col2 varchar2(10)
);
--drop table testtab_b;
create table testtab_b
(
col1 varchar2(10) primary key,
col2 varchar2(10)
);
insert into testtab_a values ('A', 'B');
insert into testtab_a values ('X', 'B');
insert into testtab_a values ('Z', 'C');

insert into testtab_b values ('A', 'B');
insert into testtab_b values ('X', 'B');
insert into testtab_b values ('Z', 'C');

CREATE OR REPLACE TRIGGER testtab_tr
BEFORE UPDATE
ON testtab_a REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
        update testtab_b
        set col1 = :new.col1,
        col2 = :new.col2
        where col1 = :old.col1;
end;

select * from testtab_a;
select * from testtab_b;

update testtab_a set col2 = 'H' where col1 = 'A';

EDIT2: If you need to go across schemas, you can use a dblink.

Inside trigger use:

            update testtab_b@someSchema
            set col1 = :new.col1,
            col2 = :new.col2
            where col1 = :old.col1;

Make sure you have proper grants setup by DBAs to do your updates, as well as any synonyms you may need (depending on your env).

Finally, if you are trying to keep these 2 tables in "sync", then don't forget about inserts and deletes (which can also be handled via similar trigger).

This is NOT the answer to replication, however, and this approach should be used very sparingly.