Cuando nuestras tablas son Maestro/Detalle necesitamos conocer cual es el identificador que se le asignó al Maestro para guardar ese identificador también en el Detalle y así poder relacionar ambas tablas. Por ejemplo:
-- Se crea la tabla Maestro
CREATE TABLE MIMAESTRO (
MAE_IDENTI INTEGER NOT NULL, -- Identificador de la tabla Maestro.
MAE_FECHAX DATE); -- Fecha de algo
-- Se establece la Primary Key de la tabla Maestro
ALTER TABLE MIMAESTRO ADD CONSTRAINT PK_MIMAESTRO PRIMARY KEY (MAE_IDENTI);
-- Se guarda la tabla Maestro en forma permanente
COMMIT;
-- Se crea el trigger que asigna valor a la Primary Key de la tabla Maestro
CREATE TRIGGER BI_MIMAESTRO_MAE_IDENTI FOR MIMAESTRO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.MAE_IDENTI IS NULL OR NEW.MAE_IDENTI = 0) THEN
NEW.MAE_IDENTI = GEN_ID(MIMAESTRO_MAE_IDENTI_GEN, 1);
END
-- Se guarda el trigger de la tabla Maestro
COMMIT;
-- Se crea la tabla de Detalles
CREATE TABLE MIDETALLE (
DET_IDENTI INTEGER NOT NULL, -- Identificador de la tabla de Detalles
DET_IDECAB INTEGER, -- Identificador de la tabla Maestro
DET_IDEPRD INTEGER, -- Identificador del Producto
DET_CANTID NUMERIC(10, 2), -- Cantidad
DET_PRECIO NUMERIC(12, 4)); -- Precio
-- Se establece la Primary Key de la tabla de Detalles
ALTER TABLE MIDETALLE ADD CONSTRAINT PK_MIDETALLE PRIMARY KEY (DET_IDENTI);
-- Se establece una Foreign Key para la tabla de Detalles
ALTER TABLE MIDETALLE ADD CONSTRAINT FK_MIDETALLE FOREIGN KEY (DET_IDECAB) REFERENCES MIMAESTRO(MAE_IDENTI) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
-- Se crea el trigger que asigna valor a la Primary Key de la tabla de Detalles
CREATE TRIGGER BI_MIDETALLE_DET_IDENTI FOR MIDETALLE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.DET_IDENTI IS NULL OR NEW.DET_IDENTI = 0) THEN
NEW.DET_IDENTI = GEN_ID(MIDETALLE_DET_IDENTI_GEN, 1);
END
-- Se guarda el trigger de la tabla de Detalles
COMMIT;
Entonces lo que ahora necesitamos es lo siguiente:
– Cuando se inserta una fila en la tabla Maestro saber cual es ese identificador para poder asignárselo a la columna DET_IDECAB de la tabla de Detalles.
¿Por qué eso?
Porque la columna DET_IDECAB es la columna que usamos para relacionar ambas tablas: MiMaestro y MiDetalle
Hay cuatro métodos que podemos usar para llegar a ese objetivo:
- Usando la función MAX()
- Usando la claúsula ORDER BY …. DESC
- Usando la función GEN_ID()
- Devolviendo un parámetro con las cláusulas RETURNING … INTO
En el método 1. escribirías algo como:
INSERT INTO MiMaestro (MAE_IDENTI, MAE_FECHAX) VALUES (0, CURRENT_DATE)
SELECT MAX(MAE_IDENTI) FROM MiMaestro
Y en el método 2. escribirías algo como:
INSERT INTO MiMaestro (MAE_IDENTI, MAE_FECHAX) VALUES (0, CURRENT_DATE)
SELECT MAE_IDENTI FROM MiMaestro ORDER BY MAE_IDENTI DESC ROWS 1
Y ambos métodos te devolverán el valor correcto de la columna MAE_IDENTI siempre y cuando no hayas escrito un COMMIT entre los comandos INSERT y SELECT (si escribiste un COMMIT entre ambos comandos podrías a llegar a tener el mismo problema que tiene el método 3.)
Sin embargo, el método 1. y el método 2. tienen un problema: en una tabla muy grande (de varios millones de filas) el SELECT demorará en mostrarte el valor de la columna MAE_IDENTI
Eso significa que debes evitar de usar los métodos 1. y 2.
Veamos ahora el método 3.
INSERT INTO MiMaestro (MAE_IDENTI, MAE_FECHAX) VALUES (0, CURRENT_DATE)
SELECT GEN_ID(MIMAESTRO_MAE_IDENTI_GEN, 0) FROM RDB$DATABASE
Lo que hace la función GEN_ID() es sumarle 0 al generador MIMAESTRO_MAE_IDENTI_GEN y devolverte el resultado. Como le suma 0, lo que te devuelve es el mismo valor que ya tenía el generador, no lo cambia. El problema es que entre el INSERT y el SELECT han transcurrido algunos milisegundos y durante esos milisegundos algún otro usuario pudo haber insertado una fila y lo que obtendrás será el valor que tiene el generador actualmente, no el que tenía cuando tú insertaste la fila.
Eso significa que debes evitar de usar el método 3.
Para hacerlo según el método 4. deberás escribir un poco más ya que se requiere de un stored procedure como el siguiente:
CREATE PROCEDURE GRABAR_MIMAESTRO(
IDENTI TYPE OF COLUMN MIMAESTRO.MAE_IDENTI,
FECHAX TYPE OF COLUMN MIMAESTRO.MAE_FECHAX)
RETURNS(
IDENTIFICADORMAESTRO TYPE OF COLUMN MIMAESTRO.MAE_IDENTI)
AS
BEGIN
INSERT INTO MIMAESTRO
(MAE_IDENTI, MAE_FECHAX)
VALUES (:Identi , :Fechax )
RETURNING MAE_IDENTI
INTO :IdentificadorMaestro;
END
Y este (finalmente, ¿verdad?) es el método correcto de hacerlo ya que en el 100% de los casos tendrás en el parámetro de salida «IdentificadorMaestro» el valor que se le asignó a la columna MAE_IDENTI y en ningún caso podrías obtener un valor erróneo y será rapidísimo.
Lo que hace este stored procedure es lo siguiente:
- Trata de insertar una fila a la tabla MiMaestro
- Ejecuta todos los triggers «Before Insert» (antes de insertar) de la tabla MiMaestro
- Si todos los triggers fueron ejecutados exitosamente, la columna MAE_IDENTI tendrá un nuevo valor
- Devuelve el valor que tiene la columna MAE_IDENTI
- Ese valor lo devuelve en el parámetro de salida cuyo nombre es IdentificadorMaestro
Por lo tanto, si quieres evitarte errores (y posiblemente algún quebradero de cabeza) cuando necesites conocer el valor de una columna utiliza un stored procedure para ello, como se ha mostrado más arriba.
El método 4. lo utilizarías así:
EXECUTE PROCEDURE GRABAR_MIMAESTRO(0, CURRENT_DATE)
Como todo stored procedure este te devolverá un cursor y en ese cursor tendrás la columna IdentificadorMaestro con su valor correspondiente.
Una ventaja adicional que tiene el método 4. es la siguiente: como la inserción se realiza dentro de un stored procedure puedes llamar a ese stored procedure desde cualquier lenguaje de programación que utilices porque el procedimiento de grabación ya está hecho. Por lo tanto puedes ejecutarlo desde Visual FoxPro, Visual Basic, Delphi, C, C++, Java, o cualquier otro lenguaje y eso te ahorrará mucho tiempo y además te permitirá trabajar en grupo con otras personas.
————————————————————————————————————————-
El problema que tienen los métodos 1., 2., 3. es que realizan dos operaciones:
- Un INSERT para insertar una fila en la tabla Maestro
- Un SELECT para averiguar cual es el número del Identificador que se guardó en esa fila
Para entender mejor el problema veamos la siguiente situación:
- El generador MIMAESTRO_MAE_IDENTI_GEN tiene el valor 524
- Se inicia la transacción T1
- Se inicia la transacción T2
- La transacción T1 inserta una fila. El generador MIMAESTRO_MAE_IDENTI_GEN ahora tiene el valor 525 y la columna MAE_IDENTI también
- La transacción T2 inserta una fila. El generador MIMAESTRO_MAE_IDENTI_GEN ahora tiene el valor 526 y la columna MAE_IDENTI también
- En la transacción T1 se escribe un SELECT para conocer cual es el valor del generador MIMAESTRO_MAE_IDENTI_GEN. Dicho SELECT por supuesto que devolverá el número 526. Pero ése no es el valor que le corresponde a la columna MAE_IDENTI de la transacción T1
No te olvides que los generadores están afuera de las transacciones, son independientes de todas las transacciones.
El método 1. y el método 2. funcionarán bien si entre el INSERT y el SELECT no has escrito un COMMIT. En el caso de haber escrito un COMMIT pueden fallar si ocurre la situación anterior. Sin embargo, el problema de esos dos métodos es la lentitud en tablas muy grandes.
El único método que nunca falla y que además siempre es rapidísimo es el 4. y por lo tanto es el recomendable.
.
Firebird SQL 
Comentarios recientes