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)

When a picture is removed from /added to/moved in the stack, all other
position-numbers should be changed accordingly.
I tried to use a trigger (after update) for this (shown below).
The problem is, that the trigger seems to be called recursively, i.e. when I
change the position number of one record,
the trigger changes all records with subsequend positions and thus calls
itself recursively.
Finally I get the message "too many concurrent executeions of the same
request".
Can anybody show me the way?


if (new.InStack=0 and old.InStack=1) then /*sheet removed from stack*/
begin
update Stack
set PosInStack=0
where ID = new.ID;

update Stack
set PosInStack = PosInStack - 1
where PosInStack > old.PosInStack;
end
else if (old.InStack=0 and new.InStack=1) then /*sheet added to stack*/
begin
update Stack
set PosInStack=1
where ID = new.ID;

update Stack
set PosInStack = PosInStack + 1
where PosInStack >= 1 and ID <> new.ID;
end
else if (new.InStack=1) then /*sheet moved to another position in
stack*/
begin
update Stack
set PosInStack = PosInStack - 1
where PosInStack >= old.PosInStack;

update Stack
set PosInStack = PosInStack + 1
where PosInStack >= new.PosInStack;
end




--
_____________________________________

Lutz Kutscher
Pattburg Poetzsch GmbH & Co KG
Tel. +49 461 773 15 60
Fax +49 461 773 15 15
LKutscher@...