ORACLE SQL TRIGGER OR STORED PROCEDURE ON TWO DIFFERENT TABLES

Question

I have two tables. I want to create a trigger on this two tables. Columns in tables are like below.

Table_A

CL_ID, TIMESTAMP_A, OUT_ID, STATUS ETC.

Table_B

OUT_ID, TIMESTAMP_B, ETC.

Data insert automatically inside this two tables (Because of entegrated systems). I need to control like this.

IF TIMESTAMP_A > TIMESTAMP_B THEN 
   UPDATE TABLE_A SET OUT_ID =' ' AND STATUS = 'ABC' 
   WHERE A.OUT_ID = B.OUT_ID

I have not too experience about trigger. I tried to create a views from this two tables using "join" and wrote a "instead of trigger" but it doesn't work correctly. Does stored procedure make more sense for this? Can someone help me this issue? Thanks in advance for helping me.

Answer 1

There are a lot of scenarios to boil down here.

What defines TIMESTAMP_A > TIMESTAMP_B ? Is any any row in the table? I assume that we are referring to a row in tableA and a partner row in tableB, where "partner" is defined by something. I'll assume OUT_ID

Are the tables 1-to-1? 1-to-many? If the latter, then what is the rule? Any timestamp? The lowest? The highest?

Even if we assume the simplest case, 1-to-1, now we have an issue of when do we implement this rule.

If I insert into tableA, then there (currently) is no row in tableB yet...Can we guarantee that there will be a tableB row eventually? Will it be part of the same transaction? What happens if we update one of the table's rows later? Do we re-execute the same logic?

So even assuming the simpler case: 1-to-1 and inserted in same transaction

then its still complicated, because there is no such thing as a "commit trigger" in Oracle, ie, you can't fire code when someone issues a commit. So ultimately, you'd look at using something like AQ to put an entry in a queue, which logs the OUT_ID for tableA, and have a second background process listening on the queue, which will then act on the message. Since it only sees that message at commit time, then by that stage, tableA and tableB will have the row you need, and the background processor can act on that.

Cross-table logic (in any database) is harder than it looks.