Helen,
I created a view as you said.
CREATE OR ALTER VIEW V_T_BALANCE(
F_ACD_ID,
NUM1,
NUM2,
BALANCE)
AS
select F_ACD_ID, NUM1, NUM2, BALANCE from FIN_ACC_CASH_DIARY
where 1 = 1
with check option
;
Then create a trigger for the view:
CREATE OR ALTER TRIGGER V_T_BALANCE_AUD0 FOR V_CASH_DIARY_BALANCE
ACTIVE AFTER UPDATE OR DELETE POSITION 0
AS
begin
/* Trigger text */
end
^
Meanwhile, in the TABLE1, the Trigger has been modified like this:
> > if (updating or (deleting) ) then
> > begin
> > select max(f_acd_id) from table1 into :maxID;
> > nid = old.f_acd_id;
> >
> > while (nid < :maxid) do
> > begin
> > select first 1 balance from V_T_BALANCE
> > where f_acd_id < :nid
> > order by f_acd_id desc
> > into :ye;
> >
> > update V_T_BALANCE
> > set balance = :ye + new.num1 - new.num2
> > where f_acd_id = :nid;
> > nid = :nid + 1;
> > end
> > end
But when I modified a record, the trigger doesn't work. Either the view or the
table, the value of Column BALANCE is not changed.
--- In [email protected], Helen Borrie <helebor@...> wrote:
>
> At 03:11 AM 29/11/2011, you wrote:
> >Hi,
> >
> >I try to update a column value in a trigger after updating a row, but it
> >raises a error of too many concurrent execution of the same request.
> >I know this is caused by recursion, but I can't avoid it.
>
> Yes, you can avoid it, as long as you are using Fb 2 or higher.
>
> >I want update a column value of all the rows which is after the updating
> >rows.
> >
> >ID ---------- Num1 ---------- Num2 --------- YE
> >1 ---------- 15 ---------- 25 ---------- 28
> >2 .......
> >3 .......-----------------------------------32
> >4 ------------20 ----------- 35 -----------27 ---> 32+20-35 =27
> >5
> >6
> >7
> >
> >if in row 4, then num1 or num2 is changed, then for the rows 4 to 7,
> >the columns YE will be changed followed.
> >
> >---------------------------------------------------------------------
> >
> >The trigger is like this:
> >
> >
> >declare variable nID integer;
> >declare variable maxid integer;
> >
> >
> > if (updating or (deleting) ) then
> > begin
> > select max(f_acd_id) from table1 into :maxID;
> > nid = old.f_acd_id;
> >
> > while (nid < :maxid) do
> > begin
> > select first 1 balance from fin_acc_cash_diary
> > where f_acd_id < :nid
> > order by f_acd_id desc
> > into :ye;
> >
> > update fin_acc_cash_diary
> > set balance = :ye + new.num1 - new.num2
> > where f_acd_id = :nid;
> > nid = :nid + 1;
> > end
> > end
> >
> >Looking forward to your help, thanks.
>
> There are a few things wrong with this approach. As you've already found,
> your attempt to use a trigger to perform DML on other rows in the same table
> will just recurse indefintely until the limit (1000 executions) kicks in and
> stops it.
>
> Another thing that's wrong (or, rather, unwise) is assuming that the current
> state of the table's data, as seen by your transaction, is the same as what
> all other transactions are seeing. In a multi-user system we depend on this
> illusion for concurrency: it's called "transaction isolation". But
> max(f_acd_id) as seen in your transaction is *not* the same as max(f_acd_id)
> that another transaction is seeing, if you have multiple users modifying this
> table. Let's hope nobody cares too much about the synchronicity of the
> calculated results you are storing there.
>
> Going back to the thing you are asking about, don't perform your updates by
> updating or deleting into the *table* that owns this trigger. Create an
> updatable view on this table for exclusive use by this trigger - that is, a
> single-table view with no computed or derived fields and having all
> non-nullable columns present (plus any nullable ones you want, of course).
> Then, write an after update or delete "do-nothing" trigger *for the view*,
> e.g.,
>
> create trigger aud_vtable for vtable
> active after update or delete as
> begin
> /* */
> end
>
> The aud_ trigger on the table will need to be granted the necessary
> privileges to update the view columns you want updated.
>
> With this approach, in Fb 2.0 and above, the update or delete ops that are
> executed from the table's trigger will fire the view's aud_ trigger and the
> table's aud_ trigger(s) will be bypassed. That will block the firing of the
> infinitely recursive trigger events that are happening with the DML callouts
> you are doing currently on the parent table.
>
> If you are still using Fb 1.5, unfortunately this won't help: in fact, it's
> likely to make matters worse. Fb 1.5 has a bug, whereby BOTH triggers would
> fire and nothing gets blocked. Not sure about 1.0...I have a vague
> recollection the bug was introduced in Fb 1.5....maybe, maybe not. It didn't
> get fixed until v.2.0, anyway.
>
> ./heLen
>
