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 firebird-support@yahoogroups.com, 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
>