--- In firebird-support@yahoogroups.com, Lutz Kutscher wrote:
> Hi,
> I want to document the order of some sheets of paper in a stack.
> Each sheet shows a different picture. My table has (mainly) four
fields:
> PictureName varchar(100),
> PosInStack integer,
> IsInStack smallInt, (Boolean, set to 1, if picture is not part
> of the stack)
> id integer (Primary Key)

I don't understand why you have PosInStack within a stack. Rather, I
recommend you to have one field called NextID. That way you would only
have to update two records whenever a record was inserted/removed from
the stack (the new or deleted record + the record with 0 in NextID or
the next record), or up to five records if a record was moved.

Generally, I think you should use a before insert record to update the
current record. However, since you need to modify other records in the
table as well, I doubt a trigger is suitable for your requirements
(UsingFirebird.pdf draft version 4 May 2002, page 517 says that "it is
important to ensure that a trigger's actions never cause the trigger
to fire, even indirectly"). Rather, create stored procedures and make
sure that all inserts/updates/deletes happen through these procedures.

Set
- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation