1

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.

Improve this question
1
  • 1
    I would advise against storing multiple values ("Twisty Tail;Candy Cane;Lucky Leaf") in a single column... Commented Sep 22, 2009 at 11:24

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
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.

Improve this answer

5 Comments

+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?
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!
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, 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.
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.