Как вот это сделать без CASE'a
...
UPDATE Personnel
SET lft = CASE
WHEN lft BETWEEN droplft AND droprgt THEN lft - 1
WHEN lft > droprgt THEN lft - 2
ELSE lft END
rgt = CASE
WHEN rgt BETWEEN droplft AND droprgt THEN rgt - 1
WHEN rgt > droprgt THEN rgt -2
ELSE rgt END;
WHERE lft > droplft;
END;
...


CREATE TABLE Personnel
(emp CHAR (10) PRIMARY KEY,
salary DECIMAL (8,2) NOT NULL CHECK (salary >=
0.00),
lft INTEGER NOT NULL UNIQUE CHECK(lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK(rgt > 0),
CHECK (lft < rgt));

Дело в том, что это MS SQL конструкция, а мне надо хотя бы близко к стандарту. У меня FireBird стоит, а там таких "подпрыжек" нет.

Таблица такая


CREATE TABLE CLASS (
CLASSID INTEGER NOT NULL PRIMARY KEY ,
NAME CHAR(64) ,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rght INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt));


Если циклом делать, примерно так:


CREATE PROCEDURE CLASS_INSERT (
CLASSID INTEGER ,
NAME CHAR (64) ,
PARENT INTEGER )
AS
DECLARE VARIABLE RIGHT_MOST_SIBLING INTEGER ;
DECLARE VARIABLE VAL_LEFT INTEGER ;
DECLARE VARIABLE VAL_RIGHT INTEGER ;
DECLARE VARIABLE VAL_ID INTEGER ;

BEGIN

SELECT WC.RGHT FROM CLASS WC
WHERE (WC.CLASSID = :PARENT)
INTO :RIGHT_MOST_SIBLING;

FOR
SELECT W1.LFT, W1.RGHT, W1.CLASSID
FROM CLASS W1
WHERE (W1.RGHT >= :RIGHT_MOST_SIBLING)
INTO :VAL_LEFT, :VAL_RIGHT, :VAL_ID
DO
BEGIN

IF (:VAL_LEFT > :RIGHT_MOST_SIBLING) THEN
VAL_LEFT = :VAL_LEFT + 2;

UPDATE CLASS WR
SET WR.RGHT = :VAL_RIGHT + 2,
WR.RGHT = :VAL_LEFT
WHERE WR.CLASSID = :VAL_ID;
END

INSERT INTO CLASS (
CLASSID,
NAME,
LFT,
RGHT)
VALUES (
:CLASSID,
:NAME,
:RIGHT_MOST_SIBLING,
(:RIGHT_MOST_SIBLING + 1));
END


то выдаёт:


Invalid insert or update value(s): object columns are
constrained - no 2 table rows can have duplicate column values.
violation of PRIMARY or UNIQUE KEY constraint "LFT" on table "CLASS".

Дело в том, что это MS SQL конструкция, а мне надо хотя бы близко к стандарту. У меня FireBird стоит, а там таких "подпрыжек" нет.

Таблица такая


CREATE TABLE CLASS (
CLASSID INTEGER NOT NULL PRIMARY KEY ,
NAME CHAR(64) ,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rght INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt));


Если циклом делать, примерно так:


CREATE PROCEDURE CLASS_INSERT (
CLASSID INTEGER ,
NAME CHAR (64) ,
PARENT INTEGER )
AS
DECLARE VARIABLE RIGHT_MOST_SIBLING INTEGER ;
DECLARE VARIABLE VAL_LEFT INTEGER ;
DECLARE VARIABLE VAL_RIGHT INTEGER ;
DECLARE VARIABLE VAL_ID INTEGER ;

BEGIN

SELECT WC.RGHT FROM CLASS WC
WHERE (WC.CLASSID = :PARENT)
INTO :RIGHT_MOST_SIBLING;

FOR
SELECT W1.LFT, W1.RGHT, W1.CLASSID
FROM CLASS W1
WHERE (W1.RGHT >= :RIGHT_MOST_SIBLING)
INTO :VAL_LEFT, :VAL_RIGHT, :VAL_ID
DO
BEGIN

IF (:VAL_LEFT > :RIGHT_MOST_SIBLING) THEN
VAL_LEFT = :VAL_LEFT + 2;

UPDATE CLASS WR
SET WR.RGHT = :VAL_RIGHT + 2,
WR.RGHT = :VAL_LEFT
WHERE WR.CLASSID = :VAL_ID;
END

INSERT INTO CLASS (
CLASSID,
NAME,
LFT,
RGHT)
VALUES (
:CLASSID,
:NAME,
:RIGHT_MOST_SIBLING,
(:RIGHT_MOST_SIBLING + 1));
END


то выдаёт:


Invalid insert or update value(s): object columns are
constrained - no 2 table rows can have duplicate column values.
violation of PRIMARY or UNIQUE KEY constraint "LFT" on table "CLASS".