heLen,

Thanks.

I found I should add a "with check option" to the end of a updatable view in the introduction of FB2.0. Now my FB version is 2.5.

But the option requires a where sub-clause, and I don't know how to do.

And Now, I have a suppose on my trigger. For the Column YE is not be updated by user (it is non-visible for users), I want to strict the execution condition of the trigger through the Column. But how to write the condition?

if (updating or (deleting)) then
begin
/* if YE is modified then leave .... */
/* it seems affect to the updating work */
end

--- 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
>