1

I have 2 columns in 2 different tables: col1 ->table1 and col2 -> table2 . One of them contains values that are not UNIQUE and the other one stores the times each values of table1 appeared. I need to create a trigger that will re-calculate col2 on update of col1. I read somewhere that you can not query the updating table in trigger, then how can i do this? Can anyone help me?

 table1:    pri_key     col1
             1           2
             2           2
             3           2
             4           3  
table2:     pri_key(col1)    col2
             1                0
             2                3
             3                1 
             4                0

p/s:sorry for the bad english

Dang Nguyen
  • 61
  • 8
  • Can you share some sample data and the expected output? – Chetan Apr 29 '17 at 11:43
  • It would usually be better and easier to have a [materialised] view that calculates the count, rather than trying to maintain a value manually like that. – Alex Poole Apr 29 '17 at 11:45
  • In fact you can, you mean table mutating when say that can't read from updatable table, but you can use compound trigger. – Seyran Apr 29 '17 at 16:14

1 Answers1

1

You'll need to use a compound trigger. An example can be found here:

Oracle trigger after insert or delete

Community
  • 1
  • 1
Prescott Chartier
  • 1,519
  • 3
  • 17
  • 34