Respuesta.

Históricamente, el manejo de errores en SQL se ha limitado principalmente a hacer rollback de la transacción después de un error fatal. Sin embargo, con el desarrollo de la lógica empresarial, cada vez se requiere con mayor frecuencia un registro preciso de errores, un rollback solo de los cambios realizados, así como la posibilidad de anidar llamadas a procedimientos (nested transactions).

El problema es que no todos los SGBD soportan completamente las transacciones anidadas (por ejemplo, en MS SQL Server son más bien niveles de puntos de guardado SAVEPOINT, y no transacciones reales), y las excepciones por errores pueden interrumpir la ejecución antes de la limpieza necesaria si no se utilizan mecanismos TRY/CATCH o similares.

Solución: usar construcciones TRY/CATCH (o análogas), SAVEPOINT para la anidación, así como configurar el comportamiento de los procedimientos al producirse errores (por ejemplo, SET XACT_ABORT en SQL Server).

Ejemplo de código (MS SQL Server):

BEGIN TRY
    BEGIN TRANSACTION
        -- operaciones
        EXEC dbo.InnerProcedure
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    -- registro de error
    INSERT INTO error_log VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH

Características clave:

Uso de TRY/CATCH para la captura de errores.
Control de @@TRANCOUNT para la anidación.
SAVEPOINT (o análogos propios para el control manual del retroceso a un punto).

Preguntas trampa.

¿Ocurre un rollback real de todos los cambios si hay un error en una transacción anidada?

En la mayoría de los SGBD, las transacciones anidadas no son transacciones separadas, sino puntos de retorno (SAVEPOINT). En caso de rollback general, se revierten todos los cambios hasta la transacción inicial (outer).

¿Qué sucede si olvido verificar @@TRANCOUNT y ejecuto ROLLBACK fuera de una transacción activa?

Se generará un error: no existe una transacción activa para revertir. Siempre verifique @@TRANCOUNT > 0 antes de ROLLBACK.

¿Funcionan los triggers y cursores en TRY/CATCH?

Sí, pero los errores en un trigger pueden provocar la transición a CATCH y el rollback de toda la transacción. Es necesario prever el manejo de errores tanto en el cuerpo del procedimiento como en los objetos llamados (procedimientos, triggers).

Errores comunes y anti-patrones

Falta de verificación de @@TRANCOUNT — intento de rollback de una transacción no activa.
COMMIT o ROLLBACK innecesarios en niveles anidados.
Registro de errores sin conservar todos los detalles (número, texto, parámetros).

Ejemplo de la vida real

Caso negativo

Varios procedimientos anidados, cada uno utiliza BEGIN TRANSACTION / COMMIT sin TRY/CATCH, ante un error, parte de los datos se modifica, parte no.

Ventajas:

Código simple

Desventajas:

Desincronización del estado
Errores poco evidentes tras una falla

Caso positivo

Toda la lógica está envuelta en TRY/CATCH, los procedimientos anidados utilizan SAVEPOINT o operan dentro de una única transacción externa, todos los errores se registran.