Automatically Update Field in Database

Question

I'd like to automatically update a database column with an aggregate of another column. There are three tables involved:

T_RIDER
  RIDER_ID
  TMP_PONYLIST
  ...

T_RIDER_PONY
  RIDER_ID
  PONY_ID

T_PONY
  PONY_ID
  PONY_NAME
  ...

T_RIDER and T_PONY have an n:m relationship via T_RIDER_PONY. T_RIDER and T_PONY have some more columns but only TMP_PONYLIST and PONY_NAME are relevant here.

TMP_PONYLIST is a semicolon spararated list of PONY_NAMES, imagine something like "Twisty Tail;Candy Cane;Lucky Leaf". I'd like to keep this field up to date no matter what happens to T_RIDER_PONY or T_PONY.

My first idea was to have triggers on T_RIDER_PONY and T_PONY. The problem is that it seems to be impossible to read T_RIDER_PONY within the trigger, I always get ORA-04091. I found some hints about working with three triggers and package variables, but this sounds way too complicated.

Maybe you think I'd better change the schema or get rid of TMP_PONYLIST completely. These are options but not the topic of this question. For the moment I'm only interested in answers that don't require any change to my applications (no application works with the tables directly, only views, so trickery with views is allowed).

So, how can I keep TMP_PONYLIST up to date automatically? How to concatenate the string is an interesting subproblem I also have not yet found an elegant solution for.

I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi.

UPDATE

I like the idea of using a materialized view. What I have is:

CREATE MATERIALIZED VIEW
  V_TMP_PONYLIST 
BUILD IMMEDIATE
REFRESH COMPLETE ON COMMIT
AS SELECT 
  R.RIDER_ID, string_agg(P.PONY_NAME) AS TMP_PONYLIST
FROM
  T_PONY P, T_RIDER R, T_RIDER_PONY RP
WHERE 
  P.PLOTGROUP_ID=RP.PLOTGROUP_ID AND
  R.QUEUE_ID=RP.QUEUE_ID 
GROUP BY R.RIDER_ID;

string_agg is not shown because it is long and the I think it is not relevant.

It won't compile with ON COMMIT, I get ORA-12054. As I understand the documentation aggregates are only forbidden with REFRESH FAST, so what's the problem here?

UPDATE Vincents and Tonys answers were different but both helpful. I accepted Tonys but be sure to read Vincents answer also.

Answer 1

Why do you need to read the T_RIDER_PONY table in the trigger? You will have either inserted or deleted a row, so all the trigger needs to do is look up the pony name in T_PONY and update table T_RIDER, either appending the name to or removing it from TMP_PONYLIST like this

create trigger t_rider_pony_trg 
after insert or delete on t_rider_pony
for each row
begin
   if inserting then
      select pony_name
      into   l_pony_name
      where  pony_id = :new.pony_id;

      update t_rider
      set    tmp_ponylist = tmp_ponylist || ';' || l_pony_name
      where  rider_id = :new.rider_id;
   elsif deleting then
      select pony_name
      into   l_pony_name
      where  pony_id = :old.pony_id;

      update t_rider
      set    tmp_ponylist = ltrim ( 
                               rtrim ( 
                                  replace(';' || tmp_ponylist || ';',
                                          ';' || l_pony_name || ';',
                                          ';'),
                                  ';', ';')
      where  rider_id = :old.rider_id;
   end if;
end;

That update in the delete section is rather unpleasant I admit; it might be preferable to use utilities like apex_util.string_to_table and apex_util.table_to_string to deal with it! See this SO answer for details.

Answer 2

Hi bbuser,

the information on the column TMP_PONYLIST is redundant (it exists somewhere else). You will get into all sorts of problems to maintain it (No solution will work correctly in a multi-user environment unless there is some sort of locking mechanism).

In a normalized model you would simply drop this column from the physical model. If you need the information, you could use a view, for example with Oracle 11gR2:

CREATE OR REPLACE VIEW rider_v AS
SELECT rider_id, /*...,*/
       (SELECT listagg(p.pony_name, ';') WITHIN GROUP (ORDER BY p.pony_name)
          FROM t_pony p
          JOIN t_rider_pony rp ON (p.pony_id = rp.pony_id)
         WHERE rp.rider_id = r.rider_id) tmp_ponylist
  FROM t_rider r;

See this SO for example of string aggregation before 11gR2.