Fórum Reordenação de Itinerário de Clientes #423335

CREATE OR ALTER trigger trg_reordenar_intinerario for clientes
active before insert or update position 0
AS
DECLARE VARIABLE VARCONT INTEGER;
DECLARE VARIABLE VARCLI_ID INTEGER;
BEGIN
    VARCONT = NEW.cli_intinerario;
    FOR
        SELECT
            CLI_ID
        FROM
            CLIENTES
        WHERE
            (cli_intinerario >= :VARCONT) AND
            (ROT_ID = NEW.ROT_ID)         AND
            (CLI_ID <> NEW.CLI_ID)
        ORDER BY
            cli_intinerario
            INTO :VARCLI_ID
    DO
    BEGIN
      VARCONT = VARCONT +1;
      UPDATE CLIENTES
         SET cli_intinerario = :VARCONT
       WHERE (CLI_ID = :VARCLI_ID) AND
            (ROT_ID = OLD.ROT_ID);
    END
END

CREATE OR ALTER PROCEDURE sp_reordenar_intinerario (
    rota integer,
    novo_int integer,
    cliente integer)
returns (
    cli_id integer,
    cli_intinerario integer,
    rot_id integer)
as
declare variable intinerario integer;
BEGIN
intinerario = :novo_int;
  FOR
    select cli_id, cli_intinerario, rot_id from clientes
    where rot_id = :rota and cli_intinerario > :novo_int and cli_id <> :cliente
    order by cli_intinerario
    INTO :CLI_ID,
         :CLI_INTINERARIO,
         :ROT_ID
  DO

   UPDATE CLIENTES SET
       cli_intinerario = :intinerario+1
       WHERE (CLI_ID = :CLI_ID) AND
             (ROT_ID = :ROT_ID);
       intinerario = intinerario;
  BEGIN
    SUSPEND;
  END
END