0

Let's say I have two tables table A and table B and they have one to many relation.

Table A

col1 | Col2

Table B

Col1 | Col3

Now I want to have a Col4 in Table A such that if any row of Col3 is true for a given Col1 value, then i want Col4 in Table A to be set as true else false.

All the updates that happend on Table B should always update my Col4 values in Table A.

Is this achievable thing by using any oracle/PL-SQL features?

Narahari
  • 21
  • 4
  • 3
    Is it possible? Sure. But you'd be far better served with a different approach. Either do the calculation in a view or create a materialized view that is 1:1 with A and does a refresh on commit to do the calculation when something changes in B. If you need to persist that calculation, it's much more likely that Oracle's built-in MV refresh logic will cause fewer problems than a bunch of manually coded triggers. – Justin Cave Oct 28 '21 at 17:19
  • 1
    I fully agree with @JustinCave. A materialized view will be much better than using a set of manually coding triggers. Better, faster and cleaner – Roberto Hernandez Oct 28 '21 at 17:22

1 Answers1

0

Use an on AFTER INSERT OR UPDATE trigger

CREATE TRIGGER sync_tables
  AFTER INSERT OR UPDATE
  OF col1
  ON tableA
BEGIN  
   INSERT INTO tableB(col1) VALUES :NEW.col1
END;
Tilen
  • 484
  • 1
  • 15