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.
.
El índice del blog Firebird21 | Firebird SQL
Jun 17, 2013 @ 04:44:46
Maestro/detalle en Visual FoxPro y Firebird | Firebird SQL
Jun 20, 2013 @ 16:34:36
Ivan K.
Sep 23, 2014 @ 21:24:49
Es posible insertar datos en dos tablas (o mas) relacionadas usando un stored procedure?
Viendo los ejemplos anteriores se me ocurre que si la tabla maestro tiene un PK autoincrementable y es posible obtenerlo, podría ser posible almacenarlo en una variable y pasarlo a otro INSERT dentro del mismo stored procedure o llamando a otro procedimiento?
Le doy vueltas al asunto, actualmente hago algo parecido desde la aplicación pero dudo que sea lo mas óptimo.
__________________________________________________________
Aprovecho para agradecer la información y las explicaciones tan claras y abundantes que proporcionan en el sitio, llevo poco tiempo con firebird y sino fuera por este blog creo que nunca lo hubiera usado.
Saludos!
wrov
Sep 24, 2014 @ 11:39:25
Sí, claro que es posible y es una práctica muy común.
Fuera de un stored procedure puedes escribir:
INSERT INTO MiTabla (MiColumna1, MiColumna2, …) VALUES (MiValor1, MiValor2, …) RETURNING MiColumna1
UPDATE OR INSERT INTO MiTabla (MiColumna1, MiColumna2, …) VALUES (MiValor1, MiValor2, …) RETURNING MiColumna1
Dentro de un stored procedure puedes escribir:
INSERT INTO MiTabla (MiColumna1, MiColumna2, …) VALUES (MiValor1, MiValor2, …) RETURNING MiColumna1 INTO :MiVariable
UPDATE OR INSERT INTO MiTabla (MiColumna1, MiColumna2, …) VALUES (MiValor1, MiValor2, …) RETURNING MiColumna1 INTO :MiVariable
Donde en MiVariable tendrías el mismo valor que se encuentra en la columna MiColumna1, porque el valor de MiColumna1 es el retornado. Por supuesto que puedes retornar cualquier columna que quieras e inclusive varias columnas.
Y muchas gracias por el buen concepto que te merece este blog, la idea es que sea útil a sus lectores. Es bueno saber que ayuda. Cualquier duda que tengas, puedes preguntar aquí o mejor en el foro:
firebird21.yoforeo.com
Saludos.
Walter.
Ivan K.
Sep 24, 2014 @ 20:59:23
Gracias wrov! He logrado resultados sobre mi duda, ahora los publico en el foro, ciertamente es mas cómodo.
JAIME DE LEON
Jul 16, 2016 @ 15:58:40
Uff me salvaste la vida
Mario Sifuentes
Mar 04, 2021 @ 15:08:55
Buen día, entiendo que el post es algo viejo ya, sin embargo, al momento de publicarlo ya existía el manejo de threads en las aplicaciones, por lo que sugiero (de ser posible) eliminar las opciones de recuperación del valor de llave primaria 1 y 2:
Usando la función MAX()
Usando la clausula ORDER BY …. DESC
Cualquiera de estas opciones producirá errores en algún momento sobre todo en bases de datos con mucha concurrencia.
Basta con hacer alguna prueba de esto creando alguna aplicación multihilos que inserte información sobre las mismas tablas para ver el fallo al que se está expuesto.
Por lo demás, agradezco al autor toda la ayuda que nos proporciona sin más ganancia que la satisfacción de poder compartir conocimiento y ayudar a los demás.
Gracias y saludos.
stusy20
Abr 05, 2024 @ 21:05:52
Hola walther una prenta estoy utilizando el firebird pero no me funciona la parte de return estoy utilizando la version 4.0 en los procedimientos almacenados…
RETURNING MAE_IDENTI saben si sea algo por la version o me pueden orientar un poco de lo que este pasando.