Forum Stats

  • 3,872,750 Users
  • 2,266,468 Discussions
  • 7,911,293 Comments

Discussions

Call a procedure only once for the last row in FOR EACH ROW trigger

Aozerov-Oracle
Aozerov-Oracle Member Posts: 2
edited Jun 7, 2011 2:48PM in SQL & PL/SQL
Hi everyone,

I wonder, if someone would help me in trigger's implementation. My use case the following:

- fire a trigger after each INSERT or UPDATE operation;
- use FOR EACH ROW algorithm for a trigger;
- collect some values of inserted/updated fields like :new.uuid and store them into a global collection defined on the package's layer;
- only for the last inserted/updated row call a procedure passing the global collection with collected values as a parameter;

Is it possible to implement for the FOR EACH ROW's type of trigger? Does a :new identifier or trigger itself have special attributes like COUNT, SIZE for defining a size of inserted/modified data for a conditional logic? Are there another possible workaround for considered use case.

Thanks,

Andrey
Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,727 Red Diamond
    Answer ✓
    Hi, Andrey,

    I suggest you use 2 triggers:
    (1) A FOR EACH ROW trigger, like you described, to collect the relevant data and store it in a global temporary table or in poackage variables. Each time this trigger fires it will overwrite any data from the previous row.
    (2) A AFTER trigger ( NOT FOR EACH ROW) that calls the procedure using the stored data.

Answers

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    The order in which rows are inserted or updated is undefined and Oracle, in general, doesn't know how many rows are going to be inserted or updated until after the statement completes. So it doesn't make a lot of sense to try to treat the "last" row, whatever that happens to be, any differently than any other row.

    Potentially, what you're looking for is a row-level trigger that populates your collection and then an after statement trigger that processes the data in the collection. But that would have to be two separate triggers (or two different components of a compound trigger in 11g). Be aware that you may also need a third trigger, a before statement trigger, that initializes your collection. Otherwise, if you raise an exception in either your row-level processing or your statement-level processing, the next statement in the same session may see residual data in the collection.

    Justin
    JustinCave
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,727 Red Diamond
    Answer ✓
    Hi, Andrey,

    I suggest you use 2 triggers:
    (1) A FOR EACH ROW trigger, like you described, to collect the relevant data and store it in a global temporary table or in poackage variables. Each time this trigger fires it will overwrite any data from the previous row.
    (2) A AFTER trigger ( NOT FOR EACH ROW) that calls the procedure using the stored data.
  • Frank, Justin,

    Thanks a lot for solutions.

    Andrey
This discussion has been closed.