Asegurando tener datos consistentes en la Base de Datos

Lo peor que le podría ocurrir a una Base de Datos es que tenga datos inconsistentes, es decir: que sean contradictorios o que falten datos que deberían estar o que estén datos que no deberían estar.

Firebird nos provee de varias herramientas que podemos usar para asegurar la consistencia, ellas son:

• Primary Key
• Foreign Key
• Unique Key
• Check
• Trigger
• Stored procedure

Con la Primary Key podemos identificar sin dudas a cada fila de la tabla. Lo ideal es que la Primary Key sea numérica y autoincremental.

Con la Foreign Key aseguramos que el valor de una columna (de la tabla hija) exista en otra columna (de la tabla padre). De esta manera siempre podremos relacionar a ambas tablas entre sí, porque tienen datos comunes.

Con la Unique Key sabemos que no habrá valores duplicados en la columna. Todos los valores serán únicos en esa columna de esa tabla.

Con el Check podemos hacer validaciones relativamente simples. Impedimos que se introduzcan valores que sabemos que no deberían estar.

Con el Trigger podemos hacer validaciones simples o muy complicadas. Esto último porque nos permite tener variables locales y entonces las condiciones de validación pueden ser tan complejas como necesitemos. También podemos usarlos para colocar el valor de una columna o para insertar, modificar o borrar filas de otras tablas.

Con el Stored procedure podemos también validar antes de realizar la operación de inserción, actualización o borrado.

Validando en un stored procedure

Aunque podemos usar los stored procedures para hacer validaciones eso no es lo aconsejable. ¿Por qué no? porque alguien podría saltarse esa validación muy fácilmente. Por ejemplo, para insertar una fila en la tabla de BANCOS podríamos tener un stored procedure que se encargue de esa tarea, y entonces en lugar de escribir:

EXECUTE PROCEDURE GRABAR_BANCO(17, 'Citibank');

alguien podría simplemente escribir:

INSERT INTO BANCOS (BAN_CODIGO, BAN_NOMBRE) VALUES (17, 'Citibank');

y así se saltaría cualquier validación que hubiéramos escrito en el stored procedure. En otras palabras las validaciones que escribimos en el stored procedure no sirvieron, nuestro trabajo no sirvió para validar.

En cambio, nadie puede saltarse la validación que escribamos en un trigger. Lo que se escribe en un trigger siempre se ejecuta.

¿Es conveniente validar en un check o en un trigger?

Podemos validar con un check cuando la validación sea relativamente simple. Por ejemplo: que el precio de venta siempre sea mayor que cero, que la fecha siempre sea igual o posterior al «01/ENE/2010», que todos los productos siempre tengan nombre, que el sexo de la persona siempre sea «F» o «M», que la cobranza siempre se haga a facturas que tienen saldo, etc.

Con un trigger podemos validar todos los casos anteriores y también cuando las validaciones son más complejas. Por ejemplo: que la fecha de la cobranza siempre sea igual o posterior a la fecha de la venta, que el descuento solamente se aplique a clientes a quienes ya se les vendió por más de 4.000 dólares y la última venta fue hace menos de 90 días y ya se les cobró al menos el 75% de lo vendido.

Además, si usamos un trigger para validar tenemos otra ventaja: podemos mostrarle al usuario mensajes personalizados. Si un check falla el mensaje que nos enviará el Firebird siempre será:

«Operation violates CHECK constraint»

en cambio, si usamos un trigger lanzamos una excepción cuando descubrimos un error y en esa excepción podemos escribir cualquier texto que deseemos, teniendo así mensajes de error personalizados.

Conclusión:

Una Base de Datos cuyo contenido es inconsistente es lo peor que puedes tener porque ninguna información que obtengas será confiable. Por ese motivo hay que evitar a toda costa tener inconsistencias en los datos. Firebird nos provee de varias herramientas muy útiles y debemos utilizarlas y sacarles el máximo provecho que para eso están.

Artículos relacionados:

Entendiendo a las Primary Keys

Claves primarias: ¿simples o compuestas?

Entendiendo a las Foreign Keys

Entendiendo la integridad referencial

Usando la restricción CHECK

Entendiendo a los Stored Procedures

Entendiendo a los triggers

Entendiendo las excepciones

El índice del blog Firebird21

Entendiendo la Integridad Referencial

La «integridad referencial» es un concepto utilizado en las bases de datos y significa que la relación entre dos tablas siempre es consistente.

¿Qué significa la palabra «integridad»?

Que no pueden sobrar ni faltar datos, deben estar todos, ninguno más y ninguno menos.

¿Qué significa la palabra «referencia»?

Que conociendo algunos datos de una tabla se pueden conocer todos los datos de la otra tabla.

¿Cómo se establece la relación entre las dos tablas?

A través de una restricción Foreign Key.

En una restricción Foreign Key una de las tablas actúa como «padre» y la otra tabla actúa como «hija». La relación entre ambas tablas está dada por una columna (o más de una columna) cuyos tipos de datos son idénticos. Por ejemplo, se puede establecer entre una columna INTEGER de la tabla «padre» y una columna INTEGER de la tabla «hija», pero no se puede entre una columna INTEGER de la tabla «padre» y una columna VARCHAR de la tabla «hija».

• SMALLINT con SMALLINT, ok
• INTEGER con INTEGER, ok
• BIGINT con BIGINT, ok
• SMALLINT con VARCHAR, error
• INTEGER con FLOAT, error
• VARCHAR con DATE, error
• etc.

¿Qué es una relación consistente?

Aquella en la cual cada fila de la tabla «hija» tiene una fila y solamente una fila correspondiente en la tabla «padre». Si una fila de la tabla «hija» no tiene una fila (y solamente una fila) correspondiente en la tabla «padre» entonces ya no existe la integridad referencial entre ambas tablas.

• 1 fila de la tabla «hija» se corresponde con 0 filas de la tabla «padre» —> no hay integridad referencial
• 1 fila de la tabla «hija» se corresponde con 1 fila de la tabla «padre» —> sí hay integridad referencial
• 1 fila de la tabla «hija» se corresponde con 2 ó más filas de la tabla «padre» —> no hay integridad referencial

¿Qué debemos tener en cuenta para establecer una integridad referencial?

Que la relación entre la tabla «hija» y la tabla «padre» se haga mediante la Primary Key de la tabla «padre» o de una Unique Key de la tabla «padre».

¿Qué implica que entre dos tablas exista una integridad referencial?

1. Que no puedes agregarle una fila a la tabla «hija» si no existe la fila correspondiente en la tabla «padre»
2. Que no puedes cambiar la columna usada en la referencia en la tabla «padre» (a menos que hayas establecido «actualización en cascada»)
3. Que no puedes borrar la fila usada en la referencia en la tabla «padre» (a menos que hayas establecido «borrado en cascada»)

¿Qué significan «actualización en cascada» y «borrado en cascada»?

• «Actualización en cascada» significa que si cambias el valor de la columna en la tabla «padre» ese mismo cambio se efectuará automáticamente en todas las filas de la tabla «hija» correspondientes
• «Borrado en cascada» significa que si borras una fila de la tabla «padre» todas las filas correspondientes de la tabla «hija» también serán borradas.

Ejemplo:

Tenemos una tabla llamada PAISES y otra tabla llamada PRODUCTOS. Queremos que la tabla PRODUCTOS referencie a la tabla PAISES, para poder así saber cual es el país del cual proceden nuestros productos.

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

La Primary Key de esta tabla está compuesta por las columnas PAI_CODSUC y PAI_IDENTI

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

Podemos establecer una restricción «Foreign Key» entre ambas tablas usando las dos columnas que tienen en común: Código de la Sucursal e Identificador del País:

ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTOS3 FOREIGN KEY (PRD_CODSUC,PRD_IDEPAI) REFERENCES PAISES(PAI_CODSUC,PAI_IDENTI);

Como ambas tablas están relacionadas mediante una «Foreign Key», entonces:

1. No podemos colocar en la columna PRD_IDEPAI un número que no exista en la columna PAI_IDENTI. O sea que el número que coloquemos en la columna PRD_IDEPAI debe existir en la columna PAI_IDENTI
2. No podemos colocar NULL en la columna PRD_IDEPAI
3. No podemos cambiar el número que se encuentra en la columna PAI_IDENTI si ese número existe en alguna columna PRD_IDEPAI
4. No podemos borrar una fila de la tabla PAISES si el número de su columna PAI_IDENTI existe en la columna PRD_IDEPAI de alguna fila de la tabla PRODUCTOS

El punto 3. podemos hacerlo si establecimos «actualización en cascada», ejemplo:

ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTOS3 FOREIGN KEY (PRD_CODSUC,PRD_IDEPAI) REFERENCES PAISES(PAI_CODSUC,PAI_IDENTI) ON UPDATE CASCADE;

El punto 4. podemos hacerlo si establecimos «borrado en cascada», ejemplo:

ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTOS3 FOREIGN KEY (PRD_CODSUC,PRD_IDEPAI) REFERENCES PAISES(PAI_CODSUC,PAI_IDENTI) ON DELETE CASCADE;

Conclusión:

Siempre que sea posible debemos tener tablas con «integridad referencial» porque eso nos asegura de tener datos consistentes en las tablas «hijas».

Como no se puede guardar en una tabla «hija» un valor que no exista en su tabla «padre» eso nos da la seguridad de que todos los valores de la tabla «hija» existan en la tabla «padre» y evitamos así tener filas «huérfanas» (se llaman así a las que no tienen «padre»). Las filas huérfanas solamente pueden causar problemas y ningún beneficio.

Artículos relacionados:

Entendiendo a las Foreign Keys

El índice del blog Firebird21

El generador autoincremental

En Firebird podemos tener unas variables públicas e internas llamadas generadores o secuencias. Son la misma cosa, lo que en Firebird siempre se llamó generador en el estandar SQL se denomina secuencia.

Que una variable sea pública significa que puede usarse en cualquier lugar dentro de la Base de Datos, o sea en cualquier SELECT, stored procedure o trigger. Que sea interna, significa que fuera de la Base de Datos no es conocida, no existe.

Aunque nosotros podemos cambiar el valor de un generador lo normal y lo correcto es que sea el propio Firebird quien lo cambie.

¿Para qué usar un generador?

Una de sus principales aplicaciones es para tener identificadores para nuestra Primary Key que podemos tener la seguridad 100% de que jamás se repetirán. Recordarás que las Primary Key no pueden tener valores duplicados, entonces como los generadores tampoco pueden tener valores duplicados (si se usan correctamente), son perfectos para ser usados en las Primary Key.

Ejemplo del uso de un generador:

SET TERM ^ ;

CREATE TRIGGER BI_PRODUCTOS_PRD_IDENTI FOR PRODUCTOS
       ACTIVE BEFORE INSERT
       POSITION 0
AS
BEGIN
   IF (NEW.PRD_IDENTI IS NULL OR NEW.PRD_IDENTI = 0) THEN
      NEW.PRD_IDENTI = GEN_ID(PRODUCTOS_PRD_IDENTI_GEN, 1);
END^

SET TERM ; ^

En este trigger tenemos un generador cuyo nombre es PRODUCTOS_PRD_IDENTI_GEN. La función GEN_ID() recibe a ese generador como parámetro e incrementa su valor en 1. ¿Por qué en 1? porque ese es el segundo parámetro que recibió la función GEN_ID(). Si queremos incrementarlo en 2 entonces habríamos escrito:

NEW.PRD_IDENTI = GEN_ID(PRODUCTOS_PRD_IDENTI_GEN, 2);

y así los valores del generador se incrementarían de 2 en 2.

También podríamos poner como segundo parámetro el número 0 ¿para qué nos serviría eso? para conocer el valor actual del generador. Eso significa que si escribimos:

SELECT GEN_ID(PRODUCTOS_PRD_IDENTI_GEN, 0) FROM RDB$DATABASE

podremos conocer cual es el valor actual del generador llamado PRODUCTOS_PRD_IDENTI_GEN

Y si lo deseamos, también podríamos usar un número negativo en el segundo parámetro.

Otra forma para cambiar el valor de un generador:

Además de usar la función GEN_ID() para cambiar el valor de un generador también podríamos hacerlo así:

SET GENERATOR PRODUCTOS_PRD_IDENTI_GEN TO 157;

cuando se ejecute ese comando el valor del generador PRODUCTOS_PRD_IDENTI_GEN será 157

Los generadores cuyos valores les asignó la función GEN_IDE() están afuera de las transacciones

Cuando la función GEN_ID() le asigna un valor a un generador, lo hace en su propia transacción, afuera de cualquier otra transacción que esté activa en ese momento. Eso significa que el valor asignado al generador permanece en él, no cambia, sin importar que la transacción finalice con un COMMIT o con un ROLLBACK.

Ejemplo:

El valor del generador antes de llamar a la función GEN_ID() es 211

Se llama a la función GEN_ID()

El valor del generador es ahora 212

La transacción donde se encuentra la función GEN_ID() terminó con un ROLLBACK

El valor del generador sigue siendo 212

¿Es conveniente cambiar manualmente el valor de un generador?

Depende de para que lo uses, si lo estás usando para asignarle valores a la columna que tienes definida como Primary Key la respuesta es un rotundo NO.

Podrías cambiarlo, si lo deseas, pero eso solamente podría acarrearte problemas y ningún beneficio.

¿Por qué no es conveniente cambiar el valor de un generador que se usa en una Primary Key?

Porque entonces pierdes la principal ventaja de usar un generador en la Primary Key: la seguridad 100% de que sus valores jamás se repetirán. Si disminuyes su valor, dos usuarios podrían intentar usar el mismo valor del generador y ese conflicto ocasionará pérdida de tiempo.

Pero las Primary Key tienen valores salteados, en una tabla pasaron del 226 al 231, faltan 4 números ahí.

Sí. ¿Y qué? ¿cuál es el problema con eso? Las Primary Key sirven para identificar a una fila exactamente, sin confusión posible. Que haya números faltantes no importa, mientras la Primary Key identifique exactamente a cada fila funciona perfectamente, esa es su utilidad, para eso sirve.

Pero a mí no me gusta que falten números, en los informes queda feo y a mis clientes tampoco les gusta

Estás confundiendo las cosas. Una cosa es la Primary Key y otra cosa distinta es el código de un ítem.

Mucha gente usa a los códigos como Primary Key pero eso es un error de concepto. Una Primary Key sirve para identificar a una fila internamente, en cambio un código sirve para identificar a una fila externamente.

Desde luego que en una tabla puedes tener ambos y eso es lo usual. Pero no confundas el uno con la otra.

La confusión ocurre porque el código pueden ser usado internamente y la Primary Key puede ser usada externamente, pero no deberían mezclarse. ¿Quieres relacionar una tabla con otra tabla? Usa la Primary Key. ¿Quiéres mostrarle una consulta al usuario? Usa el código. Y para que los informes queden bien bonitos como te gusta a tí y le gusta a tu cliente, esos códigos sí puedes numerarlos de 1 en 1, sin que falte ninguno.

Pero no cambies el valor de la Primary Key. Es realmente muy estúpido hacer eso. Te tomará más tiempo y solamente podrás tener problemas y ningún beneficio.

Artículo relacionado:

El índice del blog Firebird21

Tablas sin una PRIMARY KEY

Sabemos muy bien que todas las tablas deben tener sí o sí una Primary Key (la única excepción son las tablas que siempre tendrán una fila y nunca tendrán más de una fila) pero ¿estamos seguros de que todas nuestras tablas tienen una Primary Key?

Si una tabla (que tiene o puede tener más de una fila) no tiene una Primary Key eso es un error y debemos corregirlo urgentemente. Desde luego que puedes revisar las tablas una por una, manualmente, pero eso no sería profesional.

Aquí hay un stored procedure para averiguarlo (por supuesto que no es la única forma, pero quiero mostrar como hacerlo así):

SET TERM ^ ;

CREATE PROCEDURE TABLAS_SIN_PRIMARY_KEY
   RETURNS(
      TCNOMBRETABLA VARCHAR(32))
AS
   DECLARE VARIABLE lcNombreTabla VARCHAR(32);
   DECLARE VARIABLE lcRestriccion VARCHAR(20);
   DECLARE VARIABLE lcCC VARCHAR(32);
   DECLARE VARIABLE lcMostrar VARCHAR(1);
BEGIN

   lcCC = '***';

   lcMostrar = 'F';

   FOR SELECT
      RDB$RELATION_NAME,
      RDB$CONSTRAINT_TYPE
   FROM
      RDB$RELATION_CONSTRAINTS
   ORDER BY
      RDB$RELATION_NAME
   INTO
      :lcNombreTabla,
      :lcRestriccion
   DO BEGIN
      IF (lcNombreTabla <> lcCC) THEN BEGIN
         IF (lcMostrar = 'T') THEN BEGIN
            tcNombreTabla = lcCC;
            SUSPEND;
         END
         lcCC = lcNombreTabla;
         lcMostrar = 'T';
      END
      IF (lcRestriccion = 'PRIMARY KEY') THEN
         lcMostrar = 'F';
   END

END^

SET TERM ; ^

Este stored procedure es seleccionable y por lo tanto lo debes ejecutar así:

SELECT * FROM TABLAS_SIN_PRIMARY_KEY

Artículos relacionados:

Entendiendo a las Primary Keys

El índice del blog Firebird21

Claves primarias ¿simples o compuestas?

Las claves primarias o Primary Keys pueden ser simples (o sea, una sola columna actúa como Primary Key) o compuestas (o sea, dos o más columnas actúan como Primary Key)

¿Cuál es la mejor alternativa, cuál deberíamos emplear?

Como puedes leer en este artículo:

https://firebird21.wordpress.com/2013/03/15/entendiendo-a-las-primary-keys/

una Primary Key no puede tener valores repetidos ni valores nulos. Y eso, hay varias formas de conseguirlo.

Lo más conveniente siempre es que la columna que actúa como Primary Key (o una de las columnas, si es compuesta) sea autoincremental porque eso nos asegura que no tendrá valores repetidos ni nulos porque el mismo Firebird se encarga de asignarle valores a esa columna.

Pero ¿una columna o varias columnas?

Si planeas usar replicación entonces lo más conveniente es que la Primary Key esté compuesta por dos (o más) columnas. Si crees que nunca usarás replicación entonces puedes usar una sola columna autoincremental para ella.

La replicación es enviar los datos de una Base de Datos a otra Base de Datos. Por ejemplo tienes sucursales en Buenos Aires, Montevideo y Bogotá, cada una de ellas con su respectiva Base de Datos y quieres consolidar las ventas de esas tres sucursales para tenerlas en una sola Base de Datos. Entonces, debes poder distinguir en cual sucursal se realizó cada venta y los identificadores no te servirán porque pueden estar repetidos.

En Buenos Aires tenemos ventas con identificadores 1, 2, 3, 4, etc.

En Montevideo tenemos ventas con identificadores 1, 2, 3, 4, etc.

En Bogotá tenemos ventas con identificadores 1, 2, 3, 4, etc.

¿Cómo distinguimos entonces a cuál sucursal corresponde cada venta? Con otra columna, llamada por ejemplo CódigoSucursal.

Buenos Aires tiene el código 1

Montevideo tiene el código 2

Bogotá tiene el código 3

Entonces las ventas de Buenos Aires tendrán como Primary Key:

11, 12, 13, 14, etc.

Las ventas de Montevideo tendrán como Primary Key:

21, 22, 23, 24, etc.

Las ventas de Bogotá tendrán como Primary Key:

31, 32, 33, 34, etc.

Y nunca habrá confusión posible.

Conclusión:

• Si no usas ni planeas usar replicación entonces la Primary Key puede estar compuesta por una sola columna autoincremental
• Si usas o podrías llegar a usar replicación, entonces la Primary Key debería estar compuesta por dos columnas: una que identifique a la Sucursal y otra que sea autoincremental

Artículos relacionados:

Entendiendo a las Primary Keys

Uso de la Primary Key

El índice del blog Firebird21

 

https://firebird21.wordpress.com/2013/03/15/entendiendo-a-las-primary-keys/

Uso de la Primary Key

Como sabes, una Primary Key es una clave que tiene estas dos características:

• Sus valores son únicos (jamás se pueden repetir)
• Sus valores no pueden ser NULL

Eso nos permite identificar a cada fila (a cada registro) de una forma unívoca, es decir con la total seguridad de que estamos identificando a la fila correcta.

¿Cuándo deberíamos utilizar una Primary Key?

1. Cuando deseamos consultar la tabla en el mismo orden en que las filas fueron cargadas

Por ejemplo, escribiendo:

SELECT
   PRD_IDENTI,     -- Identificador y Primary Key
   PRD_NOMBRE,     -- Nombre del producto
   PRD_PRECTO      -- Precio de costo del producto
FROM
   PRODUCTOS
ORDER BY
   PRD_IDENTI

2. Cuando hacemos búsquedas y queremos asegurarnos de obtener las filas correctas, sin la posibilidad de equivocarnos. Por ejemplo, supongamos que queremos borrar de la tabla de PRODUCTOS al producto cuyo identificador es 12345 y cuyo nombre es ‘Jugo de naranjas’. Si escribimos:

DELETE FROM
   PRODUCTOS
WHERE
   PRD_IDENTI = 12345

estaremos seguros de borrar a un solo producto, el que tiene como identificador al número 12345.

En cambio, si escribimos algo como:

DELETE FROM
   PRODUCTOS
WHERE
   PRD_PROCED = 'Japón'

Estaríamos borrando a todos los productos cuyo país de procedencia es Japón. Podríamos creer que hay un solo producto que tiene esa procedencia pero en realidad hay varios y en ese caso los estaríamos borrando a todos, lo cual sería un error grave.

Por ese motivo, en general, cuando de borrar se trata deberíamos usar la Primary Key, es más seguro.

Valor duplicado en una Primary Key o en una Unique Key

Si una columna tiene la restricción Primary Key o la restricción Unique Key entonces no podrás tener valores duplicados en esa columna. Si lo intentas verás un mensaje similar al siguiente:

attempt to store duplicate value <visible to active transactions>in unique index RDB$PRIMARY1051

Este error ocurrirá en dos circunstancias:

1. Cuando la Primary Key o la Unique Key ya están definidas y quieres insertar un valor que ya existe
2. Cuando en una columna que tiene valores duplicados quieres definir una Primary Key o una Unique Key

 

.

 

Maestro/Detalle. Como averiguar el identificador del Maestro

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:

1. Usando la función MAX()
2. Usando la claúsula ORDER BY …. DESC
3. Usando la función GEN_ID()
4. 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:

1. Trata de insertar una fila a la tabla MiMaestro
2. Ejecuta todos los triggers «Before Insert» (antes de insertar) de la tabla MiMaestro
3. Si todos los triggers fueron ejecutados exitosamente, la columna MAE_IDENTI tendrá un nuevo valor
4. Devuelve el valor que tiene la columna MAE_IDENTI
5. 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:

1. Un INSERT para insertar una fila en la tabla Maestro
2. 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.

.