【ITニュース解説】Cursor & Trigger in SQL with Examples

SQLにおけるカーソルとトリガーは、データベースの操作をより柔軟に、あるいは自動的に制御するための重要な機能である。これらは、特定の状況下でデータの扱い方やシステムの振る舞いを細かく設定することを可能にする。

まず「カーソル」について説明する。通常、データベースからデータを取得する際には、SELECT文を使って複数の行を一度にまとめて取得する。しかし、取得した結果セット、つまり複数の行からなる表形式のデータを、プログラムの中で1行ずつ順番に処理したい場面がある。例えば、取得した各行に対して特定の計算を行ったり、複雑な条件に基づいて処理を分岐させたりする場合である。このような「結果セットを1行ずつ処理する」というニーズに応えるのがカーソルである。 カーソルは、SELECT文によって得られた結果セットに対し、データベースが用意する「ポインタ」のように機能する。このポインタが結果セットの特定の行を指し示し、その行のデータを読み出すことを可能にする。 今回の例では、Employee（従業員）テーブルから給与が50,000を超える従業員の名前と給与を1行ずつ取り出して表示する目的でカーソルを用いる。まず、Employeeテーブルを作成し、従業員ID、名前、給与の情報を格納する。このテーブルには、給与が50,000を超える従業員とそうでない従業員のサンプルデータがいくつか登録される。 カーソルを使う手順は明確に定められている。最初にDECLARE文を使ってカーソルを宣言する。この際、どのSELECT文の結果をカーソルの処理対象とするかを指定する。例では、Employeeテーブルから給与が50,000を超える従業員の名前と給与を選択するSELECT文が指定されている。 次に、OPEN文でカーソルを開く。これにより、データベースが指定されたSELECT文を実行し、その結果セットがカーソルで処理できる状態になる。 そして、FETCH NEXT文を使ってカーソルが指し示している現在の行のデータを、事前に用意した変数（例えば@EmpNameや@Salary）に格納する。複数の行を処理するためには、通常WHILEループを用いる。WHILEループの中では、@@FETCH_STATUSという特殊なシステム変数をチェックし、データが正しくフェッチできたかどうかを確認する。データが正常にフェッチできていれば、その行の情報を表示し、再びFETCH NEXTで次の行のデータを取得する。この繰り返しによって、結果セットの全行が順番に処理される。 全ての行の処理が完了したら、CLOSE文でカーソルを閉じ、DEALLOCATE文でカーソルが使用していたデータベースのリソースを解放する。これは、データベースへの負荷を軽減し、メモリなどのリソースを効率的に利用するために非常に重要である。この一連の処理の結果、給与が60,000のAlice、給与が75,000のCharlie、給与が90,000のEveといった、条件を満たす従業員の名前と給与が1行ずつ順番に表示される。

次に「トリガー」について解説する。トリガーは、データベースに対する特定の操作、例えばデータの挿入（INSERT）、更新（UPDATE）、削除（DELETE）などが発生したときに、自動的に実行される一連の処理のことである。これは、データベースに「もし〇〇というイベントが起きたら、自動的に△△という処理を実行する」というルールを設定するようなものである。手動で毎回同じ処理を行う手間を省き、データの整合性を保ったり、操作の履歴を自動的に記録したりするのに非常に有効である。 今回の例では、新しい学生がStudentsテーブルに登録された際に、その登録情報を自動的にStudent_Auditという監査用のテーブルに記録するトリガーを作成する。 まず、学生のID、名前、所属部門を管理するStudentsテーブルを作成する。次に、監査情報を記録するためのStudent_Auditテーブルを作成する。この監査テーブルには、どの学生が、どのような操作（この場合はINSERT）、いつ行われたかといった情報を格納する。AuditIDは自動で増えるIDであり、ActionDateは日時を記録する。 トリガーの作成はCREATE TRIGGER文で行う。ON Studentsと指定することで、このトリガーがStudentsテーブルに対する操作を監視対象とすることを定義する。さらに、AFTER INSERTと指定することで、Studentsテーブルにデータが挿入された「後」にこのトリガーが発動するように設定する。AFTERは、挿入操作自体が成功した後という意味であり、挿入操作が失敗した場合にはトリガーは発動しない。 トリガーの内部では、INSERTEDという特別な仮想テーブルを利用できる。このINSERTEDテーブルには、まさに今Studentsテーブルに新しく挿入された行のデータが一時的に格納されている。トリガーはこのINSERTEDテーブルから挿入された学生のIDを取り出し、Student_Auditテーブルに、その学生ID、操作内容を示す'INSERT'という文字列、そしてGETDATE()関数で取得した現在の時刻を記録する。 このトリガーが作成された後、実際にStudentsテーブルに新しい学生（例えば、Rahul）を挿入してみる。このINSERT文を実行すると、学生データがStudentsテーブルに追加されるだけでなく、トリガーが自動的に発動し、裏側でStudent_Auditテーブルにも「学生ID 101が挿入された」というログが記録される。 その後、Student_Auditテーブルの内容を確認すると、手動で何も操作していないにもかかわらず、学生の登録情報が正確に、そして自動的に記録されていることがわかる。このように、トリガーはデータベース操作に連動して特定の処理を自動化し、システムの信頼性や監査能力を高める上で非常に重要な役割を果たす。

結論として、カーソルは、SELECT文の結果セットを1行ずつ順番に処理し、各行に対して詳細な操作を行いたい場合に有効な機能である。一方トリガーは、データベースに対する特定のイベント（挿入、更新、削除）が発生した際に、事前定義された処理を自動的に実行させるための強力な仕組みである。これら二つの機能は、それぞれ異なる目的でデータベースの柔軟性と自動化を高めるために活用され、データベースアプリケーションの設計において非常に有用である。