Help with a trigger

2641697

Hi,

I have a master table and a detail table with a relationship and I want storage the row totals of the detail table into the master table. I want use a trigger to do that, but I don't want use a trigger row by row. I want to fire the trigger after the sql statment has been executed but I need to now the foreign key in the detail table to execute a count statement and update the correct row in the master page. Do you have idea how do that ? . Do you now some documentation or similar example  with my problem ?

Best regards

German G

User_6XD9J

Hello,

Can you provide table definitions and some sample data of existing records please ?

JustinCave

You can't.

Well, you could, I suppose, have an AFTER STATEMENT trigger that computed the totals for every row in the parent table every time the data for any detail row was updated.  That would be hideously inefficient but would avoid using a row-level trigger.

You could create a row-level trigger that captured the key, stored it in a collection (defined in a package or defined as part of a compound trigger if you're using 11.1 or later), and then iterate through that collection in an after statement trigger (plus a before statement trigger to reinitialize the collection).  You need the row-level trigger to capture the row-level data.  And once you're doing that, it's not obvious that the statement level trigger is doing much for you that you couldn't do in the row-level trigger itself.  Unless your processing is doing something like removing all the existing child rows and then re-inserting them or modifying a large fraction of the child rows for a particular parent.

Justin

SomeoneElse

> but I don't want use a trigger row by row.

> Do you have idea how do that ?

You can't.  :new and old can't be referenced on a table level trigger.

What is the business purpose of this count?  Keeping this column accurate in a multi user environment will be very difficult.

Maybe you should consider a view (or materialized view) instead.

Unknown

I have a master table and a detail table with a relationship and I want storage the row totals of the detail table into the master table.

Why? That denormalizes the data. That should typically only be done in a data warehousing system and then it should be done by a batch job when users are NOT performing DML on those tables.

 I want use a trigger to do that, but I don't want use a trigger row by row.

No - you do NOT want to do that. Oracle is a multi-user system. That trigger can NOT see any DML being done by other sessions. So if two users insert or update data for the same key value any totals computed by the trigger will probably be wrong.

You should NOT perform non-transactional processing in a trigger.