Implementando transacciones en procedimientos almacenados

En la siguiente entrada vamos elaborar un procedimiento almacenado que contiene una transacción la cual revertirá los cambios realizados por todas las operaciones que tengan lugar dentro del procedimiento en caso de presentarse algún fallo.

Imagenemos por un momento que tenemos un formulario de registro de empleados para una empresa en la cual se captura la información básica del empleado y además el cargo desempeñado dentro de la organización.

Para este caso hipotético se tiene una base de datos con la siguiente estructura:

• Una tabla Persona que contiene la información básica del empleado y un campo IdPersona que es auto numérico.
• Una tabla Cargo que almacena el listado de cargos que existen en la empresa.
• Una tabla Cargo_Empresa en la cual se vincula a un empleado el cargo que desempeñado en la empresa (Para este ejemplo hipotético imaginemos que una persona puede tener solo un cargo pero que por cuestiones a analizar en esta entrada se ha ubicado en una tabla externa). El modelo según lo anterior seria el siguiente.

Para poder capturar esta información vamos a hacer uso de los procedimientos almacenados (Stored Procedure). Para crear el procedimiento seleccionaremos la opción Nuevo Procedimiento Almacenado el cual esta ubicado en la seccion Programación >> Procedimientos almacenados dentro de la base de datos DB_Ejemplo tal como se aprecia en la siguiente imagen.

En el procedimiento la secuencia es operaciones es :

1. Recibir los parametros de la tabla Persona.
2. Recibir el IdCargo que tiene asignado el empleado en la organización.
3. Registrar el empleado en la tabla Persona.
4. Obtener el IdPersona, el cual es un campo numérico autogenerado (Identity) durante la inserción.
   
5. Ingresar en la tabla Cargo_Persona el IdCargo recibido y el IdPersona generado en la operación 3.

Según esta secuencia de operaciones la estructura del procedimiento es la siguiente:

Después de ejecutar el procedimiento ingresaremos unos registro en la tabla cargos. Para evidenciar la problematica generada por no implementar procedimientos almacenados en aplicaciones donde tiene lugar la realización de multiples operaciones del tipo UPDATE, INSERT y/o DELETE, ejecutaremos el procedimiento RegistrarPersona pasándole un valor erróneo (que no exite en la tabla Cargo) para el parametro IdCargo. Recordemos que dentro de la tabla Cargo_Persona existe un campo llamado IdCargo y que es llave foránea de la tabla Cargo, por lo cual existe una integridad referencial entre estas.

Después de ejecutar la instrucción de invocación (Véase archivo Exec RegistarPersona.sql) para nuestro procedimiento sin transacción tendremos el siguiente error.

Si examinamos la tabla Persona veremos que hay un registro pero en la tabla Cargo_Persona no hay , esto es debido que no se agruparon las operaciones, en este caso del tipo INSERT , bajo una transacción, esta problematica sin duda generará muchos inconvenientes de integridad en nuestro sistema si no se tienen en cuenta la momento del desarrollo de la aplicación.

Para corregir esto se agregará un bloque TRANSACTION dentro de un TRY-CATCH el cual en caso de éxito ejecutará la instrucción COMMIT TRANSACTION guardando todo la información el la base de datos y en caso de fallo ejecutará la instrucción ROLLBACK TRANSACTION la cual revertirá todas aquellas operaciones ejecutadas previamente y que esten ubicadas dentro del bloque TRY. El código de nuestro nuevo procedimiento haciendo uso de las transacciones será el siguiente.

Después de ejecutar el procedimiento volvemos a invocar las instruciones contenidas en el archivo Exec RegistarPersona.sql pasando nuevamente un valor erróneo para el parámetro IdCargo , el resultado es el siguiente.

Como se aprecia en el anterior gráfico la operación nuevamente lanzo un error de integridad referencial pero a diferencia el primer procedimiento planteando veremos que no se han guardado registros en la tabla Persona o Cargo_Persona, garantizando de esta manera la atomiciadad de las operaciones al poder manejar múltiples instrucciones y ser administradas como una sola operación.

Descripción de los archivos :

• Script Base datos DB_Ejemplo.sql :Contiene el esquema de la base de datos y lo s registros de la tabla Cargo.
• Exec RegistarPersona.sql : Contiene las instrucciones para invocar el procedimiento almacenado.
• SP RegistarPersona Con Transaccion.sql: Contiene el procedimento almacenado sin la transacción.
  
• SP RegistarPersona Sin Transaccion.sql : Contiene el procedimiento con la transacción implementada.

Descargar el código fuente de la aplicación de clic aquí.