Insertar, modificar o borrar filas en una Base de Datos externa

10 comentarios

En general, lo recomendable es que todas tus tablas estén en una sola Base de Datos ya que así puedes relacionar muy fácilmente a esas tablas entre sí. Sin embargo a veces no puedes evitar tener dos o más bases de datos. En ese caso: ¿cómo harías para insertar, modificar o borrar filas en una tabla que se encuentra en la otra Base de Datos?

El comando EXECUTE STATEMENT viene rápidamente en tu ayuda.

CREATE PROCEDURE INSERTAR_EN_BASEDATOS_EXTERNA_1
AS
   DECLARE VARIABLE lcComando VARCHAR(200);
   DECLARE VARIABLE lnIdenti  BIGINT;
   DECLARE VARIABLE lcNombre  VARCHAR(40);
BEGIN

   lnIdenti = 0;
   lcNombre = '''Esta es una prueba''';

   lcComando = 'INSERT INTO TARJETAS(TAR_IDENTI, TAR_NOMBRE) VALUES(' ||
               :lnIdenti || ',' || :lcNombre || ')';

   EXECUTE STATEMENT
      lcComando
   ON EXTERNAL
      'E:\BASESDATOS\CONTABILIDAD.FDB'
   AS
      USER 'SYSDBA' PASSWORD 'masterkey';
END;

Como puedes ver la variable lcNombre está rodeada por tres apóstrofos ¿por qué eso? porque si no se hace así ocurre un error ya que el contenido de esa variable debe estar rodeado de apóstrofos en el comando INSERT INTO. Cuando quieres que dentro de un string aparezca un apóstrofo debes escribir dos apóstrofos seguidos. Por ese motivo tuve que escribir tres apóstrofos.

Quizás también hayas notado que aunque la variable lnIdenti es numérica se la concatenó sin necesidad de convertirla previamente a tipo carácter, esa es una gran facilidad que nos da el Firebird.

En este ejemplo, los valores que insertamos en las columnas TAR_IDENTI y TAR_NOMBRE son constantes, sin embargo lo más común es que necesitemos insertar valores variables, como se ve en el siguiente ejemplo:

CREATE PROCEDURE INSERTAR_EN_BASEDATOS_EXTERNA_2(
   tnIdenti BIGINT,
   tcNombre VARCHAR(40))
AS
   DECLARE VARIABLE lcComando VARCHAR(200);
BEGIN

   lcComando = 'INSERT INTO TARJETAS(TAR_IDENTI, TAR_NOMBRE) VALUES(' ||
               :tnIdenti || ',''' || :tcNombre || ''')';

   EXECUTE STATEMENT
      lcComando
   ON EXTERNAL
      'E:\BASESDATOS\CONTABILIDAD.FDB'
   AS
      USER 'SYSDBA' PASSWORD 'masterkey';

END;

En este ejemplo los datos a insertar en la tabla externa fueron enviados como parámetros al stored procedure. Fíjate bien como el parámetro :tcNombre está rodeado por apóstrofos. Debes escribir esos apóstrofos para que funcione.

 Conclusión:

Para poder insertar, modificar o borrar filas en una tabla que se encuentra en otra Base de Datos debemos crear un string y luego ejecutarlo con el comando EXECUTE STATEMENT. Hay que tener en cuenta que las variables de tipo carácter deben estar rodeadas por apóstrofos.

Artículo relacionado:

https://firebird21.wordpress.com/2013/04/18/execute-statement/

Otorgando permisos con EMS SQL Manager

3 comentarios

Como hemos visto en este artículo:

https://firebird21.wordpress.com/2013/05/29/entendiendo-los-derechos-de-acceso/

A los usuarios, a los roles y a los objetos (vistas, triggers, database triggers, stored procedures) se les puede otorgar permisos sobre las tablas y sobre los stored procedures.

Para ello podemos utilizar el programa ISQL (que viene incluido con el Firebird) o algún programa de administración gráfica.

En la imagen de abajo vemos como usaríamos el programa ISQL para ese efecto:

GRANT1

(haciendo clic en la imagen la verás más grande)

Si usamos el programa EMS SQL Manager también podemos otorgar permisos, para ello hacemos clic con el botón derecho sobre el nombre de la tabla o del stored procedure, luego elegimos la opción “Tasks” (tareas) y luego la opción “Grants for table…” como podemos ver en la imagen de abajo:

GRANT2

(haciendo clic en la imagen la verás más grande)

 Y ahora la pantalla cambiará para mostrarnos algo similar a esto:

GRANT3

(haciendo clic en la imagen la verás más grande)

 Para otorgale permisos a un usuario hacemos clic sobre su nombre y luego sobre la operación que le queremos otorgar o sobre las opciones que vemos a la izquierda. Por ejemplo, para otorgarle al usuario “Silvia” el derecho de SELECT sobre la tabla PRODUCTOS haríamos clic allí.

GRANT4

(haciendo clic en la imagen la verás más grande)

Fácilmente podemos saber si el usuario “Silvia” tiene un permiso mirando el color del botón. Si es rojo, no lo tiene.

Para otorgarle todos los permisos podemos hacer clic sucesivamente en cada botón o elegir la opción “Grant all” que se encuentra a la izquierda:

GRANT5

(haciendo clic en la imagen la verás más grande)

Después de hacer clic sobre la opción “Grant all” esto es lo que vemos (todos los colores de los botones han cambiado y también las opciones mostradas a la izquierda han cambiado)

GRANT7

(haciendo clic en la imagen la verás más grande)

Si queremos  quitarle todos los permisos utilizaríamos la opción “Revoke all”

Importante:

Para que la acción de otorgar o de quitar permisos funcione debemos hacer clic sobre la opción “Compile”. Recién después de hacer clic sobre “Compile” el usuario tendrá o dejará de tener los permisos.

GRANT6

(haciendo clic en la imagen la verás más grande)

 Resumiendo:

  • Si el botón es de color verde, tiene el permiso
  • Si el botón es de color rojo, no tiene el permiso
  • Para otorgarle todos los permisos podemos hacer clic sobre la opción “Grant all”
  • Para quitarle todos los permisos podemos hacer clic sobre la opción “Revoke all”
  • Si elegimos “with grant option” significa que el usuario puede otorgar ese mismo permiso a otros usuarios
  • Para que el usuario tenga o deje de tener estos permisos debemos hacer clic sobre la opción “Compile”

¿En cuál carpeta tener las bases de datos?

5 comentarios

Ya instalaste el Servidor del Firebird y ahora quieres crear bases de datos (o copiar las que ya tienes) en alguna carpeta de esa computadora. Así que podrías estar preguntándote: ¿dónde? ¿en cuál carpeta?

Las carpetas donde no se recomienda que estén tus bases de datos son las siguientes:

%SYSTEMROOT%
%PROGRAMFILES%
%PROGRAMFILES(X86)%
%PROGRAMDATA%
%USERPROFILE%

Eso significa que no deben estar en C:\WINDOWS ni en C:\WINNT ni en algo similar. Tampoco en “Archivos de Programa” y tampoco dentro de algún usuario como “Walter”, “Fátima”, “Yanine”, etc.

Además la carpeta donde estarán tus bases de datos no debe estar compartida, jamás, por ningún motivo.

Ejemplos de carpetas correctas:

D:\DATABASES\

D:\MISDATOS\

E:\DB\

D:\ARCHIVOS\

E:\MISISTEMA\MISDATOS\

Recuerda que esas carpetas no deben estar compartidas o estarás comprometiendo la seguridad de tus bases de datos.

Si tienes dos discos duros entonces lo recomendable es que el Sistema Operativo se encuentre en un disco duro y tus bases de datos en otro disco duro, se consigue un mejor rendimiento de esa manera.

Y si tienes tres discos duros entonces lo ideal es que los archivos temporales se encuentren en el tercer disco duro (eso lo estableces cambiando la entrada “TempDirectories” en el archivo FIREBIRD.CONF) porque eso mejora aún más el rendimiento.

 Artículo relacionado:

El índice del blog Firebird21

 

Entendiendo los derechos de acceso

9 comentarios

En este artículo hemos visto como agregar usuarios, borrar usuarios, y cambiarles la contraseña:

https://firebird21.wordpress.com/2013/04/21/agregando-modificando-y-borrando-usuarios/

Esas personas podrán acceder a la Base de Datos pero aún no podrán realizar alguna operación en ella, ni siquiera una consulta. El Firebird por defecto les impide que ejecuten los comandos INSERT, UPDATE, DELETE, SELECT, EXECUTE PROCEDURE.

¿Por qué eso?

Porque para mantener el acceso y la utilización de la Base de Datos seguros los usuarios deben tener permisos solamente a las operaciones que específicamente se les hayan otorgado. Si no se les otorgó el permiso para realizar una operación, no podrán realizar esa operación.

Por ejemplo, a los vendedores no se les otorga el permiso para modificar los precios de venta de los productos; a los empleados no se les otorga el permiso para cambiar su propio sueldo; a los contadores no se les otorga el permiso de cambiar los datos de una venta; etc.

Si intentan realizar una operación a la cual no tienen permiso, el Firebird lo impedirá.

¿Qué es un rol?

Es el nombre otorgado a un grupo de usuarios que tienen exactamente los mismos permisos. Por ejemplo, se podría tener un rol llamado R_VENDEDORES en el cual se especificarán los permisos que poseen las personas que tienen ese rol. Todas las personas cuyo rol sea R_VENDEDORES tendrán esos permisos pero ninguno más. A los miembros de este rol se les podrían otorgar el permiso de insertar ventas (pero no modificarlas ni borrarlas), el permiso de consultar las cantidades en stock y los precios de venta de los productos (pero no insertar ni modificar ni borrar los datos de los productos). A los miembros de este rol no se les permitirá ver las compras ni las cobranzas ni los pagos ni los sueldos ni las comisiones de otros vendedores ni muchas otras cosas más.

¿Cómo se crea un rol?

Con el comando CREATE ROLE NombreRol

¿Cómo se elimina un rol?

Con el comando DROP ROLE NombreRol

¿Cuál es el comando usado para otorgarles permisos a los usuarios y a los roles?

El comando se llama GRANT (conceder, otorgar, en castellano) y su sintaxis es la siguiente:

GRANT
{<permisos> ON <objeto> | rol}
TO <concedido>
[WITH {GRANT|ADMIN} OPTION]
[{GRANTED BY | AS} [USER] otorgador]

¿Cuáles son los permisos que se pueden otorgar?

SELECT
Consultar datos
INSERT
Agregar nuevas filas
UPDATE
Modificar datos existentes
DELETE
Borrar filas
REFERENCES
Referirse a una Primary Key desde una Foreign Key. Siempre se debe 
otorgar este permiso para que puedan seleccionarse las columnas de 
la tabla referida.

ALL
Todos los anteriores

EXECUTE
Ejecuta un stored procedure o lo llama usando SELECT cuando se 
trata de un stored procedure seleccionable

ROLE
Adquiere todos los privilegios otorgados al rol. Una vez que un 
rol existe y se le han otorgado permisos, se convierte en un 
permiso que se les puede otorgar a los usuarios.

¿Qué significa WITH GRANT OPTION?

Que el usuario a quien se le otorgó ese permiso tiene el derecho de otorgarles ese mismo permiso a otros usuarios.

¿Cómo se quita un permiso?

 Con el comando REVOKE.

REVOKE <permisos>

ON <objeto>

FROM <revocado>

 Ejemplo 1. Otorgando el permiso de hacer SELECT a la tabla PRODUCTOS al usuario ALICIA

GRANT SELECT ON PRODUCTOS TO ALICIA

Ejemplo 2. Otorgando los permisos de SELECT y de INSERT y de UPDATE a la tabla PRODUCTOS al usuario SUSANA

GRANT INSERT, UPDATE, SELECT ON PRODUCTOS TO SUSANA

Ejemplo 3. Otorgando todos los permisos a la tabla PRODUCTOS al usuario GRACIELA

GRANT ALL ON PRODUCTOS TO GRACIELA

Ejemplo 4. Otorgando el permiso de cambiar el Código de Barras y el Precio de Venta de los productos al usuario INES

GRANT UPDATE (PRD_CODBAR, PRD_PREVTA) ON PRODUCTOS TO INES

Ejemplo 5. Otorgando el permiso de ejecutar el stored procedure GRABAR_PRODUCTO al usuario RAQUEL

GRANT EXECUTE ON PROCEDURE GRABAR_PRODUCTO TO RAQUEL

Ejemplo 6. Recibiendo y otorgando el permiso de hacer SELECT a la tabla PRODUCTOS

GRANT SELECT ON PRODUCTOS TO SILVIA WITH GRANT OPTION

Y ahora Silvia puede escribir:

GRANT SELECT ON PRODUCTOS TO MARCELA

GRANT SELECT ON PRODUCTOS TO LUCIANA WITH GRANT OPTION

Ejemplo 7. Otorgando permisos a un stored procedure

GRANT INSERT, UPDATE ON PRODUCTOS TO PROCEDURE GRABAR_PRODUCTO

En este ejemplo al stored procedure GRABAR_PRODUCTO se le otorgó el permiso de INSERT y de UPDATE a la tabla PRODUCTOS. Eso significa que si un usuario tiene el permiso de ejecutar el stored procedure también tendrá el permiso se insertar y de actualizar la tabla respectiva. Si no se hace así entonces habrá que asignarle a cada usuario individualmente (y a cada rol individualmente) los derechos de insertar y de actualizar la tabla, y eso llevaría mucho más tiempo.

GRANT ALL ON PRODUCTOS TO PROCEDURE RECALCULAR_SALDOS

En este ejemplo al stored procedure RECALCULAR_SALDOS se le otorgaron todos los permisos sobre la tabla PRODUCTOS. O sea que dentro de ese stored procedure se puede insertar, modificar, borrar, seleccionar datos y referenciar tablas.

Ejemplo 8. Otorgando los permisos a varios usuarios a la vez

GRANT SELECT ON PAISES TO ALICIA, GRACIELA, SILVIA, SUSANA

Estos cuatro usuarios podrán realizar consultas a la tabla PAISES

Ejemplo 9. Creando roles

CREATE ROLE R_ADMINISTRACION

CREATE ROLE R_VENDEDORES

(El nombre del rol no tiene por qué empezar con R_, esa es solamente una costumbre del autor de este blog, si quieres a tu rol lo puedes llamar: ADMINISTRACION, CONTADORES, VENDEDORES, etc., pero si el nombre empieza con R_ es más fácil saber que se trata de un rol)

Ejemplo 10. Otorgándole permisos al rol

GRANT UPDATE(PRD_PREVTA) ON PRODUCTOS TO R_ADMINISTRACION

GRANT INSERT, SELECT ON PRODUCTOS TO R_VENDEDORES

Ejemplo 11. Quitándole el permiso de hacer SELECT a la tabla PRODUCTOS al usuario ALICIA

REVOKE SELECT ON PRODUCTOS FROM ALICIA

Ejemplo 12. Quitándole todos los permisos a la tabla PRODUCTOS al usuario SUSANA

REVOKE ALL ON PRODUCTOS FROM SUSANA

Ejemplo 13. Quitándole el derecho de ejecutar el stored procedure GRABAR_PRODUCTO al usuario RAQUEL

REVOKE EXECUTE ON PROCEDURE GRABAR_PRODUCTO FROM RAQUEL

Ejemplo 14. Quitándole el derecho de otorgar permisos al usuario SILVIA

REVOKE GRANT OPTION FOR SELECT ON PRODUCTOS FROM SILVIA

Artículo relacionado:

El índice del blog Firebird21

 

Usando un script para insertar datos fijos

1 comentario

Muchas veces en nuestra Base de Datos tenemos tablas cuyas filas son fijas, por ejemplo: Estados, Provincias, Departamentos, Localidades, Bancos, Tarjetas de Crédito, etc.

Cuando trabajamos con la Base de Datos en nuestra computadora, lo normal es que agreguemos, modifiquemos y borremos un montón de filas mientras hacemos las pruebas que nos aseguren que está todo bien.

Después, cuando copiamos la Base de Datos con la cual estuvimos trabajando en la computadora del usuario: ¿cómo sabemos que los datos de todas esas tablas sean los correctos? Con tantas inserciones, modificaciones y borrados que estuvimos haciendo no podríamos asegurarlo así que una alternativa es revisar las tablas, una por una, y ponerles los valores correctos.

Eso funciona, pero es lento. En una aplicación grande tendrías cientos de tablas y si cada vez que empiezas a trabajar con ellas debes volver a verificarlas perderás muchísimo tiempo.

Hay una mejor alternativa: creando scripts que se encarguen de esa tarea.

Ejemplo. Creando un script para insertar tarjetas de crédito

En este ejemplo creamos un script que se encargará de llenar la tabla TARJETAS con las filas correspondientes. Para asegurarnos que esta tabla tiene las filas correctas solamente debemos ejecutar el script.

Listado 1.

DELETE FROM TARJETAS;

COMMIT;

SET GENERATOR TARJETAS_TAR_IDENTI_GEN TO 0;

COMMIT;

INSERT INTO TARJETAS VALUES(0, 'AMERICAN EXPRESS');
INSERT INTO TARJETAS VALUES(0, 'MASTERCARD');
INSERT INTO TARJETAS VALUES(0, 'VISA');

COMMIT;

Aquí, primero eliminamos todas las filas de la tabla TARJETAS, luego inicializamos el generador de la Primary Key a cero, a continuación le insertamos todas las filas que deseamos, finalmente escribimos el COMMIT para que los datos queden grabados permanentemente.

Por lo tanto, si ejecutamos este script en la Base de Datos que se encuentra en el Servidor del usuario la tabla TARJETAS tendrá todos sus valores correctos.

Y desde luego, así como hicimos con la tabla TARJETAS también haríamos con todas las demás tablas cuyos datos son fijos.

Artículos relacionados:

Entendiendo a los scripts

Usando scripts para documentar la Base de Datos

Ejecutando un script desde Visual FoxPro

El índice del blog Firebird21

El foro del blog Firebird21

Usando un índice descendente

3 comentarios

Cuando creamos un nuevo índice y no especificamos si lo queremos ascendente (es decir: 1, 2, 3, 4, 5, etc.) o descendente (es decir: 10, 9, 8, 7, 6, etc.) el Firebird por defecto lo crea ascendente.

¿Por qué?

Porque para la mayoría de los casos es la mejor opción, porque es la que más frecuentemente se usa.

Cuando definimos una restricción Primary Key, Foreign Key o Unique Key el Firebird automáticamente crea un índice para esas restricciones. Y ese índice es siempre ascendente.

¿Y qué sucede si se le envía como argumento a la función MAX() la columna de la Primary Key?

SELECT
   MAX(PRD_IDENTI)     /* PRD_IDENTI es la Primary Key de la tabla PRODUCTOS */
FROM
   PRODUCTOS

Si miramos el PLAN de esta consulta veremos que no usa un índice:

INDICE1

(haciendo clic en la imagen la verás más grande)

¿Por qué no se usó un PLAN?

Porque el índice de la Primary Key, el índice que el Firebird automáticamente crea para esa restricción es siempre ascendente. Si queremos que la función MAX() use un índice en esta consulta entonces tenemos que crear ese índice de forma descendente.

CREATE DESC INDEX IDX_PRODUCTOS ON PRODUCTOS(PRD_IDENTI);

Si después de crear un índice descendente para la Primary Key nos fijamos cual es el PLAN que se usará en la consulta de arriba esto será lo que veremos:

INDICE2

(haciendo clic en la imagen la verás más grande)

 O sea que ahora sí el Firebird está usando un índice.

Conclusión:

Verifica siempre cual es el PLAN de tus consultas, a veces agregándoles un índice se puede obtener una ganancia en velocidad impresionantemente grande.

Artículos relacionados:

https://firebird21.wordpress.com/2013/04/30/usando-un-plan/

https://firebird21.wordpress.com/2013/05/03/algo-mas-sobre-plan/

Usando subconsultas en expresiones

1 comentario

Antes de la versión 2.5 del Firebird no se podían usar subconsultas en expresiones aunque dichas subconsultas retornaran un solo valor. Esto nos obligaba a usar la construcción SELECT … INTO

Ahora, estas asignaciones ya son posibles dentro de un stored procedure, un trigger o un execute block:

MiVar = (SELECT … FROM …);

IF ((SELECT … FROM …) = 1) THEN …

IF(25 = ANY(SELECT … FROM …)) THEN …

IF(2104 IN (SELECT … FROM …)) THEN …

Por supuesto que en los dos primeros ejemplos el SELECT debe retornar una sola fila.

lcNomCli = (SELECT CLI_NOMBRE FROM CLIENTES WHERE CLI_IDENTI = 11111);     /* Nombre del cliente */

lnPreVta = (SELECT PRD_PREVTA FROM PRODUCTOS WHERE PRD_IDENTI = 12345);     /* Precio de venta del producto */

Fíjate que debes rodear al SELECT con paréntesis o recibirás un mensaje de error.

Entendiendo a las Foreign Keys

14 comentarios

Una Foreign Key (en castellano: clave extranjera o clave foránea) es una columna (o varias columnas) de una tabla que se corresponden exactamente con una columna (o varias columnas) de otra tabla donde están definidas como Primary Key o como Unique

De esta forma se arma una relación padre/hijo, también conocida como maestro/detalle o como cabecera/detalle entre ambas tablas.

¿Para qué sirven las Foreign Keys?

Para asegurarnos que en la tabla hija solamente se graben valores que existen en la tabla padre.

Así, nunca tendríamos una venta que no se sabe a cual cliente se le vendió, o un producto que se vendió pero no se sabe el número de la Factura de venta, o un alumno a quien se le tomó un examen pero no se sabe cual examen se le tomó, o un cheque se emitió pero no se sabe el nombre del beneficiario de ese cheque, etc.

Si usamos Foreign Keys el mismo Firebird nos impedirá que grabemos una fila hija si no existe el padre de dicha fila. Por lo tanto, sería imposible que nos encontremos con errores como los descritos arriba.

Es una importantísima medida de seguridad y como tal es altamente recomendable que usemos Foreign Keys porque eso le dará mucha confiabilidad a nuestra Base de Datos.

¿Cómo se crea una Foreign Key?

Primero, la tabla padre debe existir y tener un identificador único (este identificador único puede ser una restricción Primary Key o una restricción Unique)

Segundo, a la tabla hija se le agrega una columna (o más de una columna) que correspondan al mismo tipo de datos que la Primary Key o la Unique de la tabla padre. Eso significa que si el identificador de la tabla padre es BIGINT, el identificador que se usa en la tabla hija para relacionarlo con la tabla padre también debe ser BIGINT. Si el identificador del padre es INTEGER, el identificador en la tabla hija usado para la relación también debe ser INTEGER, etc. Los nombres de los identificadores no importan, pero los tipos de datos sí, pues deben ser idénticos.

Ejemplo:

(Para facilitar la comprensión utilizaremos el programa EMS SQL Manager, pero cualquier otro programa administrador gráfico también servirá o podríamos hacer todo esto manualmente con el programa ISQL que viene incluido con el Firebird.)

FOREIGN1

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

En esta imagen se muestran dos columnas de la tabla CLIENTES (tiene muchas más columnas, pero no nos interesan ahora). Como se puede ver, el tipo de datos de la columna CLI_IDENTI (identificador del cliente) es de tipo BIGINT. Esta es la tabla que actuará como padre.

FOREIGN2

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

Esta tabla se llama MOVIMCAB y se usa para guardar en ella las cabeceras de los movimientos. Aquí, la columna donde se grabará el identificador del cliente también es de tipo BIGINT, por lo tanto podemos utilizarla sin problemas. Fíjate que esta tabla tiene dos identificadores: el que identifica a cada movimiento ocurrido y el que identifica a cada cliente.

Si hacemos clic en la pestaña “Constraints” y luego clic en la pestaña “Foreign Keys” y luego clic con el botón derecho, veremos este menú contextual:

FOREIGN3

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

haciendo clic allí veremos una ventana de nombre “Constraints” la cual nos permite definir a la nueva Foreign Key. Podemos elegir su nombre, la columna o columnas de la tabla hija, el nombre de la tabla padre y la o las columnas de la tabla padre, y las reglas que se aplicarán cuando se borre o se modifique una fila de la tabla padre.

FOREIGN4

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

Como puedes ver en la Captura 4., se relacionó la columna MVC_IDECLI con la columna CLI_IDENTI de la tabla CLIENTES. ¿Qué significa eso? que desde este momento en la columna MVC_IDECLI solamente se podrá ingresar un número que ya exista en la columna CLI_IDENTI.

¿Qué son las reglas de integridad?

Si miras en la parte inferior de la imagen de arriba verás que dice “On Delete Rule” y “On Update Rule“. ¿Qué significa eso?

Son las acciones que debe realizar el Firebird cuando se modifica (UPDATE) o se borra (DELETE) una fila de la tabla padre. Esas reglas de integridad son las siguientes:

NO ACTION

Es la opción por defecto. Impide que el identificador de la tabla padre sea cambiado o que la fila padre sea borrada. Si se intenta una de esas operaciones el Firebird se enojará.

FOREIGN5

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

Foreign key references are present for the record” significa que hay al menos una fila en alguna tabla hija (porque una tabla padre puede tener muchas tablas hijas) con el mismo identificador que se quiso cambiar o borrar. Como la regla de integridad es NO ACTION, eso no está permitido.

 ON UPDATE CASCADE

Si se cambia el identificador de la tabla padre también se cambia el identificador relacionado en la tabla hija. Cuidado con esto porque si la tabla hija tiene millones de filas entonces cambiar todos los identificadores puede llevar un buen tiempo. Recuerda que en este caso al cambiar el identificador de la fila padre, se cambian los identificadores de todas las filas hijas, en todas las tablas que tengan la regla de integridad ON UPDATE CASCADE. En general, no es bueno hacer algo así.

FOREIGN6

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

En la Captura 6. podemos ver los valores originales del identificador del cliente. En ambos casos el identificador del cliente es 1 y está todo ok, así debe ser. En la columna MVC_IDECLI de la tabla MOVIMCAB se introdujo el número 1 y como hay un cliente que tiene ese identificador la grabación se realizó sin problemas. Después se cambió el identificador en la tabla CLIENTES, se reemplazó el 1 por un 2 y el Firebird automáticamente cambió el valor de la columna MVC_IDECLI, como podemos ver en la imagen de abajo.

FOREIGN7

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

El cambio de identificador en la columna MVC_IDECLI fue automático. Luego de cambiar el identificador en CLI_IDENTI de 1 a 2 y ejecutar el COMMIT correspondiente, ese mismo número 2 apareció en la columna MVC_IDECLI.

ON DELETE CASCADE

Si se borra una fila de la tabla padre también se borran todas las filas relacionadas de la tabla hija. ¡¡¡Cuidado con esto!!! porque puede ser muy peligroso. En nuestro ejemplo, si borras al cliente también estarás borrando todas las ventas que se le hicieron a ese cliente. Hay que pensarlo muy bien antes de establecer como regla de integridad a ON DELETE CASCADE, porque un error de apreciación puede ser trágico.

SET NULL

El identificador en la tabla hija es puesto a NULL. Eso implica que la fila hija se convierte en huérfana ya que no tiene padre. Evidentemente esta regla de integridad no puede ser aplicada si la columna de la tabla hija no admite NULL.

SET DEFAULT

El identificador de la tabla hija es puesto a su valor por defecto. Cuando definimos una columna podemos establecer que tendrá un valor por defecto cuando ningún valor le es asignado explícitamente. Ese será el valor que tendrá el identificador de la tabla hija cuando el identificador de la tabla padre sea cambiado o la fila padre sea borrada. Algunos puntos que se deben recordar son:

      1. El valor por defecto es el que estaba siendo usado cuando la restricción Foreign Key fue definida. Si más tarde se cambió ese valor por defecto no importa, el valor original será el utilizado. Cuidado con esto.
      2. Si ningún valor por defecto fue declarado para la columna entonces su valor por defecto es NULL. En ese caso, es lo mismo que usar la regla de integridad SET NULL
      3. Si el valor por defecto no existe en el identificador de la tabla padre, ocurrirá un error

¿Cómo se puede ver el comando SQL que crea a la Foreign Key?

En la misma ventana llamada “Constraints“, a la izquierda, hay una opción rotulada “DDL“. Haciendo click en ella nos muestra el comando SQL correspondiente a esa Foreign Key, como puedes ver en la imagen de abajo:

FOREIGN8

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

Nos dice que se modificó la tabla MOVIMCAB para agregarle una restricción llamada FK_MOVIMCAB, la cual es una Foreign Key que relaciona a la columna MVC_IDECLI de la tabla MOVIMCAB con la columna CLI_IDENTI de la tabla CLIENTES.

Y aquí está la forma en que se crea una tabla y sus restricciones si no usas un administrador gráfico (en este artículo se usó EMS SQL Manager, hay varios más, para ser productivo es aconsejable que uses alguno porque ahorrarás muchísimo tiempo). Esto es lo que escribirías en el programa ISQL o en un script:

CREATE DOMAIN D_IDENTIFICADOR AS BIGINT;

CREATE DOMAIN D_CHAR3 AS CHAR(3);

CREATE DOMAIN D_FECHA2010 AS DATE CHECK (VALUE >= '01-01-2010');

CREATE TABLE MOVIMCAB (
   MVC_IDENTI D_IDENTIFICADOR NOT NULL,
   MVC_TIPMOV D_CHAR3,
   MVC_IDECLI D_IDENTIFICADOR,
   MVC_FECHAX D_FECHA2010);

ALTER TABLE MOVIMCAB ADD CONSTRAINT PK_MOVIMCAB PRIMARY KEY (MVC_IDENTI);

ALTER TABLE MOVIMCAB ADD CONSTRAINT FK_MOVIMCAB FOREIGN KEY (MVC_IDECLI) REFERENCES CLIENTES(CLI_IDENTI);

 Conclusión:

Debemos usar mucho a las Foreign Keys porque con ellas se consigue que el contenido de nuestra Base de Datos sea altamente confiable. Si las usamos evitaremos que nos falten datos o que se realicen acciones inconsistentes, como borrar un producto del cual tenemos ventas registradas, o un alumno que ya se inscribió, o una cuenta contable que ya fue usada en un asiento, etc.

La regla de integridad NO ACTION es la más restrictiva y la que en general es mejor aplicar para no tener filas huérfanas, algo que en muchos casos puede ser muy malo, por eso es la regla de integridad por defecto.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Una forma rápida de escribir un SELECT

1 comentario

A veces, queremos escribir un SELECT el cual tendrá todas o casi todas las columnas de una tabla. Si queremos todas las columnas podríamos usar el asterisco * pero esa es una práctica no recomendada.

Si usamos el programa EMS SQL Manager tenemos una alternativa interesante y que nos facilitará el trabajo: hacemos clic con el botón derecho sobre el nombre de la tabla, luego elegimos la opción “Script to New SQL Editor” y luego la opción “Select”, como se ve en esta imagen:

SELECT1(haciendo clic en la imagen la verás más grande)

Y esto es lo que obtenemos:

SELECT2

(haciendo clic en la imagen la verás más grande)

O sea, un SELECT con todas las columnas de la tabla. Esto puede ahorrarnos mucho tiempo, sobre todo con tablas que tienen decenas de columnas.  Este SELECT podemos ahora “copiarlo y pegarlo” donde lo necesitemos.

 

Registrar los cambios a los precios de venta

1 comentario

Muchas veces no nos interesa registrar los cambios a todas las columnas de una tabla sino solamente los cambios a una columna determinada, un ejemplo muy frecuente se relaciona con los precios de venta.

En estos artículos hemos visto como se pueden registrar todas las operaciones que se realizan a una tabla:

https://firebird21.wordpress.com/2013/05/24/registrando-los-cambios-realizados-a-los-datos-de-una-tabla/

https://firebird21.wordpress.com/2013/05/24/otro-ejemplo-de-trigger-de-actualizacion/

Ahora, veremos como registrar solamente los cambios a los precios de venta de los productos. Para ello creamos una tabla llamada PRECIOS_VENTA que tiene esta estructura:

PRECIOSVENTA1

(haciendo clic en la imagen la verás más grande)

Y a la tabla PRODUCTOS le agregamos este trigger:

SET TERM ^ ;

CREATE TRIGGER PRODUCTOS_BIU FOR PRODUCTOS
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
BEGIN

   IF (INSERTING) THEN     -- Si se está insertando un nuevo producto
      INSERT INTO PRECIOS_VENTA
          VALUES (NEW.PRD_CODSUC, 0, CURRENT_TIMESTAMP, NEW.PRD_IDENTI, NEW.PRD_PREVTA);

   IF (UPDATING AND NEW.PRD_PREVTA <> OLD.PRD_PREVTA) THEN     -- Si se está actualizando y cambió el precio de venta
      INSERT INTO PRECIOS_VENTA
          VALUES (NEW.PRD_CODSUC, 0, CURRENT_TIMESTAMP, NEW.PRD_IDENTI, NEW.PRD_PREVTA);

END^

SET TERM ; ^

Como puedes ver, cada vez que se inserta un nuevo producto a la tabla de PRODUCTOS se inserta también una fila a la tabla de PRECIOS_VENTA, para registrar en ella cual es el precio de venta de ese producto.

Además, cada vez que se actualiza una fila de la tabla PRODUCTOS y el precio de venta nuevo es distinto al precio de venta anterior también se inserta una fila a la tabla de PRECIOS_VENTA. Fíjate que solamente se realiza la inserción si el precio de venta cambió, si el precio de venta es el mismo entonces no se realiza la inserción, ya que no se tiene necesidad de hacerla. NEW.PRD_PREVTA es el precio de venta que se quiere grabar y OLD.PRD_PREVTA es el precio de venta que actualmente está guardado en la columna. Las seudovariables NEW y OLD son muy útiles porque nos permiten comparar si lo que ya tenemos guardado en una columna es igual o es distinto al valor que ahora queremos guardar en esa misma columna.

Ejemplo:

Para verificar el funcionamiento del trigger se agregó una fila a la tabla de PRODUCTOS y luego se modificó 2 veces su precio de venta. Este es el resultado obtenido:

PRECIOSVENTA2

(haciendo clic en la imagen la verás más grande)

 Como puedes ver, funcionó perfectamente. En la tabla PRECIOS_VENTA ahora tenemos guardados el precio de venta original y también cada uno de los cambios a ese precio de venta.

Conclusión:

A veces no necesitamos guardar los cambios a todas las columnas de una tabla sino solamente los cambios a algunas columnas específicas, un ejemplo típico es el de los precios de venta.

Older Entries