• Articolo

Rollback e commit in stored procedure e trigger

È possibile eseguire un'istruzione Transact-SQL ROLLBACK TRANSACTION o COMMIT TRANSACTION in una stored procedure o in un trigger, ma questa operazione può generare errori.

Stored procedure

Se al termine dell'esecuzione di una stored procedure il valore di @@TRANCOUNT è diverso dal valore alla chiamata della stored procedure, viene generato l'errore informativo 266. Ciò può essere dovuto a due motivi:

  • Per chiamare la stored procedure viene specificato per @@TRANCOUNT un valore di 1 o maggiore e la stored procedure esegue un'istruzione ROLLBACK TRANSACTION. Il valore di @@TRANCOUNT viene ridotto a 0 e al completamento della stored procedure viene generato un errore 266.

  • Per chiamare la stored procedure viene specificato per @@TRANCOUNT un valore di 1 o maggiore e la stored procedure esegue un'istruzione COMMIT TRANSACTION. Il valore di @@TRANCOUNT viene ridotto di 1 e al completamento della stored procedure viene generato un errore 266. Se tuttavia si esegue BEGIN TRANSACTION dopo COMMIT TRANSACTION, l'errore non viene generato.

Trigger

Un trigger opera come se, al momento dell'esecuzione, fosse presente una transazione attiva, indipendentemente dal fatto che l'istruzione che attiva il trigger sia una transazione implicita o esplicita.

Se un'istruzione viene eseguita in modalità autocommit, esiste un'istruzione BEGIN TRANSACTION implicita che, in caso di errore, consente il recupero di tutte le modifiche generate dall'istruzione. Questa transazione implicita non produce alcun effetto sulle altre istruzioni del batch perché, al completamento dell'istruzione, ne viene eseguito il commit o il rollback. Quando viene chiamato un trigger, tuttavia, la transazione implicita risulta attiva.

Ogni volta che viene eseguito un trigger, viene avviata una transazione implicita. Se il trigger completa l'esecuzione e @@TRANCOUNT = 0, verrà generato un errore 3609 e il batch verrà interrotto. Se un'istruzione BEGIN TRANSACTION viene eseguita in un trigger, crea una transazione nidificata. In questa situazione, quando viene eseguita un'istruzione COMMIT TRANSACTION, questa viene applicata solo alla transazione nidificata.

Quando si utilizza ROLLBACK TRANSACTION in un trigger, tenere presente quanto segue:

  • Viene eseguito il rollback di tutte le modifiche dei dati eseguite fino al punto specifico della transazione corrente, incluse le modifiche eseguite dal trigger.

  • Il trigger continua l'esecuzione delle istruzioni successive all'istruzione ROLLBACK. Se tali istruzioni modificano i dati, non viene eseguito il rollback delle modifiche eseguite.

  • Un'istruzione ROLLBACK in un trigger comporta la chiusura e la deallocazione di tutti i cursori dichiarati e aperti nel batch contenente l'istruzione che ha attivato il trigger, inclusi i cursori dichiarati e aperti nelle stored procedure chiamate dal batch che ha attivato il trigger. Vengono chiusi solo i cursori dichiarati in un batch precedente al batch che ha attivato il trigger. I cursori STATIC o INSENSITIVE rimangono tuttavia aperti nei casi seguenti:

    • L'opzione CURSOR_CLOSE_ON_COMMIT è impostata su OFF.

    • Il cursore statico è sincrono oppure si tratta di un cursore asincrono completamente popolato.

Per eseguire un rollback parziale in un trigger, anziché utilizzare ROLLBACK TRANSACTION è possibile utilizzare l'istruzione SAVE TRANSACTION.