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 un trigger termina l'esecuzione con @@TRANCOUNT = 0, verrà generato un errore 3609 e il batch verrà interrotto. Nei trigger è pertanto consigliabile evitare l'utilizzo di ROLLBACK TRANSACTION, che reimposta @@TRANCOUNT su 0, e di COMMIT TRANSACTION, che può ridurre il valore di @@TRANCOUNT a 0. L'esecuzione dell'istruzione BEGIN TRANSACTION dopo un rollback eviterà che venga generato l'errore, ma potrebbe provocare errori nella logica dell'applicazione.
È importante capire che l'esecuzione di un'istruzione BEGIN TRANSACTION nel trigger determina in effetti l'inizio di una transazione nidificata. In questa situazione, l'istruzione COMMIT TRANSACTION successiva verrà applicata solo alla transazione nidificata. Poiché l'istruzione BEGIN TRANSACTION nidificata viene ignorata durante il rollback delle transazioni nidificate, l'istruzione ROLLBACK TRANSACTION eseguita nel trigger esegue il rollback oltre le istruzioni BEGIN TRANSACTION eseguite dal trigger. ROLLBACK esegue il rollback fino alla transazione esterna e imposta @@TRANCOUNT su 0.
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.
Vedere anche
Concetti
Nidificazione delle transazioni
Altre risorse
@@TRANCOUNT (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
ROLLBACK WORK (Transact-SQL)
SAVE TRANSACTION (Transact-SQL)