3 Replies Latest reply: Dec 20, 2007 5:53 AM by 9554 RSS

    Journalling consecutive updates in same row and same commit

    144315
      Hi everybody,

      My problem looks like this:

      First, let say that I have a business rule for journalling updates occurring in a table T.

      I have a another business rule that fires when I update a column C1 in table T; this business rule executes an UPDATE statement to update another column C2 in the same row according to the new value of column C1.

      My problem is that the change in column C2 does not appear in the journalling table. It is almost like the second trigger (update of C2) is not taken into account even though the update of C1 appears in the journalling table.

      Does anybody have a clue ?

      Thanks,
      Denis.
        • 1. Re: Journalling consecutive updates in same row and same commit
          144315
          Would anybody have the revision 6.5.3.7 of package hsu_capf.pkb from Headstart ? It may contain the fix for my bug.
          • 2. Re: Journalling consecutive updates in same row and same commit
            144315
            My best guess is that the journalling business rule is executed before the business rule containing the UPDATE statement, which would mean that the parameters of the journalling business rule would not correspond to the final values.

            Does anybody have an idea how to fix this problem ?

            Thanks.
            • 3. Re: Journalling consecutive updates in same row and same commit
              9554
              Hi Denis,

              I discovered that we have a similar problem met journalling after not finding some changes which are done automatically by other business rules.
              The problem occured met CEV rules which are fired first.
              A workaround is inserting the journalling record manually if the recoding of the change is essential.

              Example (xxx = alias of table):
              declare
              ...
              l_xx_rec cg${table_name}.cg$row_type;
              l_result boolean;
              ...
              if not qms_transaction_mgt.not_on_stack_yet('BR_xxx_JRN_UPD')
              then
              l_xxx_rec.id := p_xxx_id;
              cg${table_name}.slct(l_xxx_rec); -- retrieve the current values
              l_result := dvs_xxx_capi.br_xxx_jrn_upd(p_id => l_xxx_rec.id
              , ... {other parameters}
              );
              end if;

              It should be solved in a newer relase of Headstart. But because the application in which this occurs is still on 8i we cannot yet upgrade to the release of Headstart in which this fixed (it is certified on 9i and up because of the use of some 9i and up features).