En muchas ocasiones tenemos que escribir datos en una tabla cabecera (padre/maestra) y en una o más tablas de detalle (hijas).

Esas tablas tienen una relación entre ellas a través de una columna que tiene exactamente el mismo valor en ambos casos. O sea que el valor que existe en una columna de la tabla cabecera se encuentra en una columna de la tabla detalle.

Ese valor en común puede ser un Identificador (que es lo recomendable) o un Código (que no es lo recomendable pero algunos lo hacen así)

Ambas tablas son relacionadas mediante una restricción Foreign Key y de esta manera:

  • Si conocemos el Identificador de la tabla cabecera entonces podemos conocer todas las filas de la tabla detalle que están relacionadas
  • Si conocemos el Identificador de la cabecera de la tabla detalle entonces podemos conocer cual es la fila de la tabla cabecera relacionada
  • Podemos evitar que se borre una fila de la tabla cabecera si tiene filas relacionadas en la tabla detalle

Entonces, como la relación se hace mediante el Identificador de la tabla cabecera, ¿cómo nos aseguramos que las tablas detalle tengan ese mismo Identificador en la columna que relaciona ambas tablas?

Veamos un ejemplo:

CABDET01

Captura 1. Si haces clic en la imagen la verás más grande

En la Captura 1. podemos ver las primeras columnas de la tabla de VENDEDORES. En la columna VEN_IDENTI guardamos el Identificador de cada Vendedor.

CABDET02

Captura 2. Si haces clic en la imagen la verás más grande

En la Captura 2. podemos ver las primeras columnas de la tabla MOVIMCAB (cabecera de movimientos). En la columna MVC_IDENTI guardamos el Identificador de cada fila de cabecera y en la columna MVC_IDEVEN (Identificador del Vendedor) guardamos el Identificador de unos de los vendedores, en el caso que sea necesario guardarlo.

CABDET03

Captura 3. Si haces clic en la imagen la verás más grande

En la Captura 3. podemos ver las primeras columnas de la tabla MOVIMDET (detalles de movimientos). En la columna MOV_IDENTI guardamos el Identificador de cada fila de detalle, en la columna MOV_IDECAB guardamos el Identificador de la cabecera y en la columna MOV_IDEPRD guardamos el Identificador del producto.

Por lo tanto, antes de guardar las filas de la tabla MOVIMDET debemos conocer cual es el valor de la columna MVC_IDENTI ya que el valor que tiene esa columna será el que guardaremos en la columna MOV_IDECAB de la tabla MOVIMDET.

El procedimiento debe ser así:

Abrir una transacción para lectura/escritura
Hallar el número que se guardará en la columna MVC_IDENTI
Guardar ese número en la columna MVC_IDENTI
Insertar una fila a la tabla MOVIMCAB
Para cada fila de la tabla MOVIMDET relacionadas:
Guardar el valor de MVC_IDENTI en la columna MOV_IDECAB
Insertar una fila en la tabla MOVIMDET
Cerrar la transacción

Fíjate que la transacción se abre antes de hallar el número que se guardará en la tabla MVC_IDENTI.

¿Y cómo se halla ese número?

Lo recomendable es usar un generador (también llamado secuencia) porque eso nos asegurará que no existan jamás identificadores repetidos. Los valores de los generadores son obtenidos automáticamente por el Firebird fuera de cualquier transacción y así podemos estar absolutamente seguros de que no se repetirán, inclusive aunque haya más de un millón de usuarios conectados a nuestra Base de Datos los valores generados serán siempre distintos, que es lo que necesitamos.

 Para insertarle una fila a una tabla, el Firebird nos provee de dos comandos:

INSERT

UPDATE OR INSERT

El primero es el tradicional y el que existe en todos los motores SQL. El segundo es específico del Firebird y es muy útil porque nos ahorra escribir muchas líneas de código fuente.

Ambos comandos pueden devolver el valor de una (o más de una) columna si así lo deseamos.

Devolviendo el valor de una columna estando fuera de un stored procedure:

INSERT INTO MiTabla (MiColumna1, MiColumn2, MiColumna3, ...) VALUES (MiValor1, MiValor2, MiValor3, ...) RETURNING MiColumna1

En este caso, se retorna el valor de MiColumna1 (o de cualquier otra columna, e inclusive de varias columnas si queremos).

Devolviendo el valor de una columna estando dentro de un stored procedure:

INSERT INTO MiTabla (MiColumna1, MiColumn2, MiColumna3, ...) VALUES (MiValor1, MiValor2, MiValor3, ...) RETURNING MiColumna1 INTO :MiVariable1

La primera forma no funcionará dentro de un stored procedure, pero sí funcionará la segunda forma. ¿Por qué eso? porque dentro de un stored procedure no se puede usar directamente el valor de una columna, siempre hay que usar una variable, y para eso sirve la cláusula INTO, para colocar el valor de una columna dentro de una variable, y esa variable será la que usaremos dentro del resto del stored procedure.

Ejemplo 1:

Este stored procedure se encarga de actualizar o insertar una fila de la tabla MOVIMCAB, o sea la tabla cabecera.

CREATE PROCEDURE GRABAR_MOVIMCAB_VENTAS(
      CodSuc TYPE OF COLUMN MOVIMCAB.MVC_CODSUC,
      Identi TYPE OF COLUMN MOVIMCAB.MVC_IDENTI,
      TipMov TYPE OF COLUMN MOVIMCAB.MVC_TIPMOV,
      IdeVen TYPE OF COLUMN MOVIMCAB.MVC_IDEVEN)
   RETURNS(
      ftnIdenti TYPE OF COLUMN MOVIMCAB.MVC_IDENTI)
AS
BEGIN

   UPDATE OR INSERT INTO MOVIMCAB
            (MVC_CODSUC, MVC_IDENTI, MVC_TIPMOV, MVC_IDEVEN)
     VALUES (:CodSuc    ,:Identi   , :TipMov   , :IdeVen   )
  RETURNING MVC_IDENTI
       INTO :ftnIdenti;

END;

Este stored procedure usamos para guardar la fila de cabecera, es decir la fila de la tabla MOVIMCAB. Fíjate que el stored procedure devuelve un parámetro de salida, el cual se llama ftnIdenti, eso significa que el valor de ftnIdenti será conocido por quien llamó al stored procedure.

El valor de MVC_IDENTI (que será el que devolveremos en el parámetro de salida ftnIdenti) lo obtenemos mediante un trigger como el siguiente:

CREATE TRIGGER BI_MOVIMCAB_MVC_IDENTI FOR MOVIMCAB
   ACTIVE BEFORE INSERT
   POSITION 0
AS
BEGIN
   IF (NEW.MVC_IDENTI IS NULL OR NEW.MVC_IDENTI = 0) THEN
      NEW.MVC_IDENTI = GEN_ID(MOVIMCAB_MVC_IDENTI_GEN, 1);
END;

Fíjate que si el valor que se quiere guardar en la columna MVC_IDENTI es cero o es NULL entonces se llama a la función interna del Firebird GEN_ID(), la cual nos devolverá un número único, que jamás estará repetido.

 Ejemplo en Visual FoxPro:

Como muchos de los lectores de este blog utilizan o conocen el lenguaje Visual FoxPro entonces aquí se muestra como haríamos en ese lenguaje:

M.MVC_CODSUC = 0
M.MVC_IDENTI = 0
M.MVC_TIPMOV = "SVT"
M.MVC_IDEVEN = 25     && o cual sea el Identificador del vendedor

=Abrir_Transaccion_ABM()

lcComando = "EXECUTE PROCEDURE GRABAR_MOVIMCAB_VENTAS(?M.MVC_CODSUC, ?M.MVC_IDENTI, ?M.MVC_TIPMOV, ?M.MVC_IDEVEN)"

llComandoOK = SQLEXEC(gnHandle, lcComando) = 1

IF llComandoOK
   M.MVC_IDENTI = ftnIdenti
   * Para cada fila de movimiento de detalle que queremos grabar, escribiríamos algo como:
      M.MOV_CODSUC = M.MVC_CODSUC
      M.MOV_IDENTI = 0
      M.MOV_IDECAB = M.MVC_IDENTI
      M.MOV_IDEPRD = 2571     && o cual sea el valor del Identificador del producto
      lcComando = "EXECUTE PROCEDURE GRABAR_MOVIMDET(?M.MOV_CODSUC, ?M.MOV_IDENTI, ?M.MOV_IDECAB, ?M.MOV_IDEPRD)"
      llComandoOK = SQLEXEC(gnHandle, lcComando)
ENDIF

=Cerrar_Transaccion()

¿Qué hicimos aquí?

Primero abrimos una transacción para lectura/escritura. Luego ejecutamos el stored procedure GRABAR_MOVIMCAB_VENTAS el cual nos devolvió una variable llamada ftnIdenti, la cual contiene el valor del Identificador de la cabecera en la tabla MOVIMCAB. Ese valor de ftnIdenti lo guardamos en la variable M.MVC_IDENTI, para no perderlo y poder usarlo todas las veces que querramos. Luego, grabamos todas las filas de detalle, asegurándonos que el Identificador de la cabecera (es decir, la columna MOV_IDECAB) sea el correcto. Y finalmente cerramos la transacción.

Conclusión:

Para asegurarnos que el Identificador de la cabecera sea utilizado en la columna relacionada de la tabla de detalles debemos hacer todo el proceso dentro de una misma transacción. De esa manera será imposible que otra transacción interfiera con la nuestra. Y por lo tanto, siempre tendremos la relación correcta.

Artículos relacionados:

El generador autoincremental

El índice del blog Firebird21

El foro del blog Firebird21