ストアド プロシージャとトリガでのロールバックとコミット

  • [アーティクル]

ストアド プロシージャまたはトリガ内で Transact-SQL ステートメントの ROLLBACK TRANSACTION または COMMIT TRANSACTION を実行できますが、エラーが発生することがあります。

ストアド プロシージャ内

@@TRANCOUNT の値がストアド プロシージャの実行開始時と、プロシージャの完了時とで異なる場合、情報エラー 266 が発生します。この問題は、次の 2 つの状況で発生します。

  • 1 より大きい @@TRANCOUNT でストアド プロシージャが呼び出され、ストアド プロシージャで ROLLBACK TRANSACTION ステートメントが実行された。@@TRANCOUNT が 0 に減少し、ストアド プロシージャが完了するときにエラー 266 が発生します。
  • 1 より大きい @@TRANCOUNT でストアド プロシージャが呼び出され、ストアド プロシージャによって COMMIT TRANSACTION ステートメントが実行された。@@TRANCOUNT が 1 減少し、ストアド プロシージャが完了するときにエラー 266 が発生します。ただし、COMMIT TRANSACTION の後で BEGIN TRANSACTION が実行されると、エラーは発生しません。

トリガ内

トリガは、トリガの実行時に有効な未完了のトランザクションが存在するかのように動作します。このことは、トリガを起動したステートメントが暗黙のトランザクション内にある場合にも明示的なトランザクション内にある場合にもあてはまります。

ステートメントの実行を自動コミット モードで開始する場合、暗黙の BEGIN TRANSACTION が存在するので、エラーが検出されると、そのステートメントが生成したすべての変更を復旧できます。この暗黙のトランザクションは、バッチ内の他のステートメントには影響しません。ステートメントの完了時にこのトランザクションはコミットまたはロールバックされるためです。ただし、トリガが呼び出された時点では、この暗黙のトランザクションはまだ有効です。

トリガが実行されるときに、暗黙のトランザクションが開始されます。@@TRANCOUNT = 0 でトリガの実行が完了すると、エラー 3609 が発生して、バッチが終了します。このため、トリガの内部では、@@TRANCOUNT が 0 にリセットされる ROLLBACK TRANSACTION、および @@TRANCOUNT が 0 に減少する COMMIT TRANSACTION を使用しないようにすることをお勧めします。ロールバック後に BEGIN TRANSACTION ステートメントを実行することで、エラーが発生しないようにできますが、アプリケーション ロジックで問題が発生する場合があります。

トリガで発行される BEGIN TRANSACTION ステートメントは、実際には、入れ子になったトランザクションを開始することを理解することが重要です。この状況では、COMMIT TRANSACTION ステートメントの実行は、入れ子になったトランザクションにのみ適用されます。入れ子になった BEGIN TRANSACTION ステートメントは入れ子になったトランザクションのロールバック時には無視されるので、トリガ内で実行された ROLLBACK TRANSACTION はトリガ自体によって実行されたそれまでのすべての BEGIN TRANSACTION ステートメントをロールバックします。ROLLBACK によって最も外側にあるトランザクションにロールバックされ、@@TRANCOUNT が 0 に設定されます。

トリガで ROLLBACK TRANSACTION を使用するときは、次の動作に注意してください。

  • 現在のトランザクションのその時点までに加えられたすべてのデータ変更 (トリガによって行われた変更も含む) がロールバックされます。
  • ROLLBACK ステートメントの実行後、トリガは残りのすべてのステートメントを継続して実行します。これらのステートメントのいずれかがデータを変更する場合、その変更はロールバックされません。
  • トリガ内の ROLLBACK は、トリガを起動したステートメントが入っているバッチ内で宣言され、開かれたすべてのカーソルを閉じて割り当てを解除します。これには、トリガを起動したバッチが呼び出したストアド プロシージャ内で宣言され、開かれたカーソルも含まれます。トリガを起動したバッチの前のバッチで宣言されたカーソルは閉じられるだけです。ただし、次の場合、STATIC カーソルまたは INSENSITIVE カーソルは開かれたままになります。
    • CURSOR_CLOSE_ON_COMMIT が OFF に設定されている。
    • 静的カーソルが同期カーソルであるか、完全にデータが設定された非同期カーソルである。

ROLLBACK TRANSACTION を使用する代わりに、SAVE TRANSACTION ステートメントを使用して、トリガ内で部分的なロールバックを実行できます。

参照

概念

入れ子構造のトランザクション

その他の技術情報

@@TRANCOUNT (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
ROLLBACK WORK (Transact-SQL)
SAVE TRANSACTION (Transact-SQL)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手