MensajePublicado: Jue 17 Feb, 2005 12:50 pm    Asunto: Campos auto-numericos consecutivos en Interbase/Firebird. Responder citando
Bueno aquí pongo la solución a un problema que surgió en el foro sobre Base de Datos, es sencillo de solucionar y de ver como funciona, lo he hecho con triggers, porque así es totalmente transparente a la ejecución de cualquier cliente ya sea mediante consultas o mediante aplicación.

OJO no se refieren a inserciones en ORDEN, sino que los números queden en orden, indistintamente de si las tuplas se insertan antes o despues, es decir, esta solución simplemente no deja huecos en id_articulo. ok?

En principio debe funcionar indistintamente del exito o no de las transacciones, pero si teneis algun duda, postead.

Lo bueno de usar triggers es que no tenemos que preocuparnos de llamarlos, ellos solos saltaran o no dependiendo de que estemos haciendo. Así solo tenemos que preocuparnos de insertar, modificar y borrar datos en la tabla articulos, pero no del número de codigo que se le asigna a cada articulo, y tampoco debemos preocuparnos de la tabla disponibles.

1º Establecemos las tablas y generador a usar.

Código:

/* Tabla para articulos */
CREATE TABLE articulos (
     id_articulo INTEGER NOT NULL,
     descripcion VARCHAR(100)
);

/* Tabla para números disponibles */
CREATE TABLE disponibles (
     numero INTEGER NOT NULL
);

/* claves primarias */
ALTER TABLE articulos ADD CONSTRAINT articulos_PK PRIMARY KEY (id_articulo);
ALTER TABLE disponibles ADD CONSTRAINT disponibles_PK PRIMARY KEY (numero);

/* Generador a usar para id_articulo en articulos */
CREATE GENERATOR codigo_articulo_gen;
SET GENERATOR codigo_articulo_gen TO 0;


2º Creamos 3 "tristes" triggers para controlar las inserciones, bajas y modificaciones de articulos.

INSERCION
Código:

/* Insercion */
SET TERM ^;
CREATE TRIGGER crear_id_articulo FOR articulos
ACTIVE BEFORE INSERT POSITION 0
AS
   DECLARE VARIABLE num_tuplas INTEGER;
BEGIN
   /* Vemos si hay numeros disponbiles */
   /* No lo hago directamente porque en triggers no puedo comparar con NULL */
   SELECT COUNT(*) FROM disponibles INTO :num_tuplas;
   
   IF (:num_tuplas=0) THEN
      /* Si no hay numeros disponibles pues creamos uno con el generador */
      NEW.id_articulo=GEN_ID(codigo_articulo_gen, 1);
   ELSE
   BEGIN
      /* Si hay disponibles, tomamos el más pequeño como id_articulo */
      SELECT MIN(numero) FROM disponibles INTO NEW.id_articulo;
      /* Y además lo borramos de los números disponibles */
      DELETE FROM disponibles WHERE numero=NEW.id_articulo;
   END
END
^


El trigger se dispará el primer POSITION 0 y antes de confirmar la inserción. Como se puede ver el trigger de inserción toma un número si hay disponible en la tabla disponibles, sino pues genera un nuevo número.

------------------------------------------------------------------------------------
BAJAS
Código:

/* AHORA hacemos BAJAS de articulo */
SET TERM ^;
CREATE TRIGGER borrar_articulo FOR articulos
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
   /* Simplemente inserto el codigo disponible en la tabla de disponibles */
   INSERT INTO disponibles (numero) VALUES (OLD.id_articulo);
END
^


Este trigger se disparará el primero (POSITION 0) justo despues de saber que se ha confirmado la baja. Y simplemente hacemos que el id_articulo elimanda quede disponible para futuras inserciones o modificaciones. No he controlado que ese número ya esté, pero si la tabla disponibles no se toca a mano, no debería dar problemas, aunque también se puede controlar ese error.

------------------------------------------------------------------------------------
MODIFICACION
Código:

SET TERM ^;
CREATE TRIGGER modifica_articulo FOR articulos
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
   /* Si los codigos difieren es que se cambia el codigo de la nueva tupla modificada */
   IF (NEW.id_articulo<>OLD.id_articulo) THEN
   BEGIN
      /* Insertamos el nuevo número disponible en la tabla disponibles */
      INSERT INTO disponibles (numero) VALUES (OLD.id_articulo);
      /* Y borramos el número que asignamos en la modifcación por si este estuviera en disponibles */
      DELETE FROM disponibles WHERE numero=NEW.id_articulo;
   END
END
^


La modificación también se realizar cuando se sabe que se confirma, y lo que hacemos es insertar el id_articulo que se queda disponible en la tabla de disponibles y borrar el hipotetico número nuevo que se pretende asignar por si estuviera en disponibles.

------------------------------------------------------------------------------------
Bueno pues en principio, yo creo que eso es todo, haced pruebas, metiendo datos.

Ciao chicos.
_________________
OJO!! Ya tenemos subforo de Bases de Datos!!
Gracias a los admins.