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 >
