views:

147

answers:

2

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.

+1  A: 

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.

Tony Andrews
+1 Cool solution, I thought string manipulation was way too comlicated, but your solution is really nice. At the moment tmp_ponylist is sorted. Do you think it would be difficult to extend your solution that it keeps tmp_ponylist sorted?
bbuser
It would be possible to sort the list, but I think you'd need to use either a temporary table or a user-defined type to store it in and select back from in order. Or write yourself some bubble-sort code to work on a PL/SQL collection!
Tony Andrews
I don't think this will work in a multi-user environment. User A inserts a row trigger updates T_Rider; User B inserts a row, doesn't see A's change; A blocks B's update; A commits, releases lock, B updates; B commits, A's changes gone
Daniel Emge
@Daniel, I don't think so: I didn't SELECT the current value from T_RIDER and then update it, I just updated it. Once A commits and releases the lock, B gets the row to update and sees A's change before making its own.
Tony Andrews
It can fail on mutating table with multi-table inserts and updates through join views, and maybe get deadlock issues when dealing with concurrent non-single row inserts/updates. Look into having tmp_ponylist in a separate on commit materialized view.
Gary
@Tony, You're right. That specific case won't happen.
Daniel Emge
+3  A: 

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.

Vincent Malgrat
TMP_PONYLIST was introduced for performance reasons. We may get rid of TMP_PONYLIST but I am still exploring the other options. I like the idea of using a view, but it needs to be a materialize one. See my update of the question.
bbuser