Answer.

Historically, error handling in SQL has been limited to rolling back the transaction after a fatal error. However, as business logic has developed, there is an increasing need for precise error logging, rolling back only the changes that were made, and the ability for nested procedure calls.

The problem is that not all DBMS fully support nested transactions (for example, in MS SQL Server these are more like SAVEPOINT save levels rather than true transactions), and exceptions upon errors can interrupt execution before necessary cleanup occurs if TRY/CATCH mechanisms or analogues are not used.

Solution: use TRY/CATCH constructs (or similar), SAVEPOINT for nesting, and configure procedure behavior on errors (for example, SET XACT_ABORT in SQL Server).

Example code (MS SQL Server):

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

Key features:

Using TRY/CATCH to catch errors.
Monitoring @@TRANCOUNT for nesting.
SAVEPOINT (or equivalent manually controlled return points).

Trick questions.

When an error occurs in a nested transaction, does a real rollback of all changes occur?

In most DBMS, nested transactions are not separate transactions but return points (SAVEPOINT). During a global rollback, all changes are rolled back to the starting (outer) transaction.

What happens if you forget to check @@TRANCOUNT and execute ROLLBACK outside an active transaction?

An error will be raised — there is no active transaction to roll back. Always check @@TRANCOUNT > 0 before ROLLBACK.

Do triggers and cursors work in TRY/CATCH?

Yes, but errors in triggers can cause a transition to CATCH and roll back the entire transaction. Error handling must be provided both in the procedure body and in called objects (procedures, triggers).

Common errors and anti-patterns

Lack of @@TRANCOUNT checking — attempting to roll back an inactive transaction.
Unnecessary COMMIT or ROLLBACK at nested levels.
Logging the error without saving all details (number, text, parameters).

Real-life example

Negative case

Several nested procedures, each using BEGIN TRANSACTION / COMMIT without TRY/CATCH, result in partial data changes during an error.

Pros:

Simple code

Cons:

State desynchronization
Non-obvious errors after failure

Positive case

All logic is wrapped in TRY/CATCH, nested procedures use SAVEPOINT or operate within one outer transaction, all errors are logged.