Hi Thierry,

For you first problem, you could do like this:

Update Mytable set qty=qty - Avalue;
Delete Mytable Where qty = 0;

and include these 2 queries into a procedure. Please refer to
Firebird reference material to execute stored procedure with SELECT.

The second problem could be arised if you update examined field
with 'T' on other record[s] in the same table. I guess you put
master-child record in the table. For example, you want to update
the flag 'T' on all record with the same master ID, and this master
ID is a field that distinguish master-child record in table.

CREATE TABLE Mytable {
ID int,
MasterID int,
qty int,
Flag varchar(1)
}

If this is the case, I suggest you either store master-child record
in seperate table or make the flag 'T' in NULL to child record.
Because you may fire endless afterupdate trigger when you're
updating a row. In second solution, you could obtain master-child
record by using:

SELECT * FROM Mytable WHERE
(Flag='T') or (MasterID in (SELECT ID FROM Mytable WHERE Flag='T'));

Of course, this solution must slow down the respond time without
question.

Please give the create table script of Mytable, if my anticipation
is wrong.

Regards,

Terry
a DB hobbyist

--- In firebird-support@yahoogroups.com, Teträm Corp <dev@...> wrote:
>
> terryleonardo a écrit :
> > Hi Thierry,
> >
> > If your 2nd trigger expect to qty must be greater than 0, you
should
> > use constrain instead of trigger. Because you can not delete the
> > same row when the table engaged in your updating.
> >
> i do not understand how i can describe a constraint to replace my
trigger
> could you explain?
> > If your intension is deleting a row in mytable through an update
> > query, you had better to make it with delet and update query
> > separately.
> >
> it is difficult to do that, because the use is :
> update mytable set qty = qty - avalue;
> so in the same query, some rows downs to 0 but other downs to more
than
> 0 (avalue had been calculated to not be greater than the lesser
qty)
> > In the 3rd trigger, you did not mention which table that the
trigger
> > lies. I assume it it attached in the same table, and you could
> > update new.field[s] within the trigger.
> >
> you're right it is the same table
> i can't just change new.field[s] values because:
> 1 - the procedure does more than just an update (some selects, and
> inserts in other tables) and not only on the current record
> 2 - the update could be done not throw the trigger so must be in a
procedure
> > Regards,
> >
> > Terry
> > a DB hobbyist
> >
> > --- In firebird-support@yahoogroups.com, Tetram Corp <dev@>
wrote:
> >
> >> is there a way to break a trigger chain?
> >>
> >> on a table, i've got 3 triggers after update. First is not
revelant
> >>
> >> second does:
> >> if (new.qty = 0) then
> >> delete from mytable where id = new.id
> >>
> >> third does:
> >> if (new.field = 'T') then
> >> execute procedure update_mytable;
> >> else
> >> execute procedure update_othertable;
> >>
> >> the problem is that, sometimes, update_mytable assign 0 to the
> >>
> > field qty.
> >
> >> so i've got an error saying "too many execution of the same
query"
> >>
> > (not
> >
> >> sure about the message but sense is there)
> >>
> >> the solution i used to solve my problem was to change the third
> >>
> > trigger to
> >
> >> if ((new.field = 'T') and (new.qty > 0)) then
> >> etc
> >>
> >> except using an exception, is there a way i could use to break
the
> >> trigger chain ?
> >>
> >> Thierry
> >>
> >>
>