Sorry.
The description is a part of my trigger, some infomation was omitted.
Originally, I want to explain it simply, but difficult to see.
The trigger is a After Update & Delete trigger.
TABLE1 structure (show simply)
ID ---------- Num1 ---------- Num2 --------- BALANCE
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 BALANCE will be changed followed.
The defination (TABLE1 -- table name):
CREATE OR ALTER TRIGGER TABLE1_AUD0 FOR FIN_ACC_CASH_DIARY
ACTIVE AFTER UPDATE OR DELETE POSITION 0
AS
declare variable ye numberic(7,2);
declare variable nID integer;
declare variable maxID integer;
begin
ye = 0;
nid = 0;
maxid = 0;
if (updating or (deleting) ) then
begin
select max(f_acd_id) from Table1 into :maxID; /* find the max id in table1
for looping high range*/
nid = old.f_acd_id;
while (nid < :maxid) do /*loop all records and update Column BALANCE
after the cursor which have been modified or deleted */
begin
select first 1 BALANCE from TABLE1
where f_acd_id < :nid
order by f_acd_id desc
into :ye;
update TABLE1
set BALANCE = :ye + new.NUM1 - new.NUM2
where f_acd_id = :nid;
nid = :nid + 1;
end
end
end
--- In firebird-support@yahoogroups.com, "Leyne, Sean" <Sean@...> wrote:
>
>
>
> > The trigger is like this:
>
> What is the TRIGGER declaration, what table? BEFORE or AFTER??
>
>
>
> > 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;
>
> "Ye" is not declared as a variable.
>
> If you are trying to refer to the "Ye" column, only "old.ye" and "new.ye"
> context variables are available.
>
>
> Sean
>
