SQL トリガー: エッセンシャルガイド

公開: 2022-10-12

トリガーは、SQL Server のデータベースエンティティです。技術的には、特定のデータベース操作に応答する特定のクラスの関数呼び出しです。

この重要なガイドは、あなたの職業に非常に役立つ SQL トリガーに関する詳細な情報を提供します。始めましょう！

SQL トリガーとは

「トリガー」という言葉は、データベースのコンテンツが変更されるたびに、サーバーがクエリを自動的に実行するステートメントを表します。

トリガーは、メモリ空間に格納される特別な名前の SQL クエリのグループです。これは、データベースイベントが発生するたびにすぐに呼び出される特定のタイプの関数呼び出しです。すべてのトリガーにはテーブルが割り当てられています。

たとえば、特定のテーブルに新しい列が追加されたとき、または特定のレコードが変更されたときに、トリガーをアクティブにすることができます。

Microsoft Developer Network によると、トリガーはストアドプロシージャの特定のクラスです。トリガーステートメントでは、最初にトリガーを実行するタイミングを定義し、次にトリガーがアクティブ化された後に実行するアクションを指定します。

構文：

 CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];

各パラメータの説明

CREATE TRIGGER trigger_name – トリガーを構築するか、既存のトリガーの名前を変更するために使用されます。
BEFORE/AFTER – このクエリは、トリガーの実行時間 (特定のイベントの前または後) を定義するために使用されます。
INSERT/UPDATE/DELETE – これは、テーブルに対して実行したいアクションを表します。
ON tableName – ここでは、トリガーを設定するテーブル名を定義します。
FOR EACH ROW – このステートメントは行トリガーに関連しています。つまり、行が変更されるたびにトリガーが実行されます。
trigger_body – トリガーがアクティブ化されたときに実行するアクションを指定します。

トリガーは、既に実行されてメモリに安全に保存されたクエリを再利用できる、独自の ID を持つ保存された関数です。それでは、SQL がそれらを必要とする理由を理解してみましょう。

トリガーは主に、イベントが発生するたびにコードの展開を規制するために使用されます。別の言い方をすれば、特定のイベントに応答して特定のコードフラグメントを常に実行する必要がある場合、トリガーの使用は理想的な選択肢です。

以下は、SQL データベース操作でトリガーを利用する利点の一部です。

影響を受けるテーブルからデータを挿入、更新、または削除するときに、追加のチェックを実行します。
応答時間が短縮され、コンピューティング費用の増加に役立ちます。
初期制約ではアクセスできない高度なデフォルトパラメータのエンコードを有効にします。

参照整合性は、リレーショナルデータベースシステムの主要なコアプロパティです。これは、データベースシステムに保持されるデータが、各トランザクションおよび各操作に対して常に正確でなければならないことを意味します。

2 つのテーブルが別々のデータベースまたはシステムに配置されている場合、制約値を使用してテーブル内のデータを確実に検証する方法はありません。このような状況では、トリガーが実行の唯一のオプションです。

トリガー引数の組み合わせ

すべてのテーブルに対して、6 つの異なるトリガータイプを指定できます。これらは、SQL 行レベルトリガーに含まれるトリガー引数の組み合わせです。

BEFORE INSERT : これらのトリガーは、指定されたテーブルまたはデータベースで INSERT 操作を実行する前に、行に対してアクションを実行します。

AFTER INSERT : データベースの INSERT アクティビティの直後に行に対してアクションを実行します。

BEFORE UPDATE : これらのトリガーを使用すると、データベースで UPDATE アクションが実行される前に、行に対する関数が実行されます。

AFTER UPDATE : データベースまたは特定のテーブルの UPDATE アクティビティの直後に行に対してアクションを実行します。

BEFORE DELETE : データベースまたはテーブルが DELETE アクションを受ける前であっても、行に対して特定の操作を実行します。

AFTER DELETE : これらのトリガーは、DELETE トランザクションごとに行に対してアクションを実行します。

SQL トリガーの種類

SQL トリガーは、特定のイベントが発生したときにすぐに実行されるストアドファンクションです。これは、イベント駆動型のスケジューリングに似ています。その後の状況で、トリガーの実行を開始できます。

DML トリガー– DML は Data Manipulation Language の略です。 DML トリガーを使用すると、データ変更に反応してコードを実行できます。このトリガーは、INSERT、UPDATE、DELETE などの DML コマンドが実行されるとアクティブ化されます。これらは「テーブルレベルトリガー」とも呼ばれます。

DDL トリガー– DDL は Data Definition Language の略です。 DDL トリガーを使用すると、テーブルの追加や削除などのデータベーススキーマの変更や、ユーザーのチェックインなどのサーバーイベントに反応してコードを実行できます。これらは「データベースレベルトリガー」と呼ばれます。

これらのトリガーは、アクティブなデータベースで CREATE、ALTER、DROP などの特定の DDL ステートメントが実行されたときにアクティブ化できます。これらは、実行されるアクティビティを監視および管理するためにも利用できます。

LOGON トリガー– LOGON (スタートアップ、ログイン、ログアウト、シャットダウン) イベントが発生すると、ログオントリガーが即座に呼び出されます。これらは、ユーザートランザクションが開始される前であっても、ユーザー認証プロセスに従ってのみ実行されます。認証が失敗した場合、LOGON トリガーはトリガーされません。

これらのトリガーを使用して、ログイン履歴を記録したり、特定のログインのイベント制限を確立したり、サーバー接続の他の監査および ID 管理機能を実行したりできます。

CLR トリガー– CLR は Common Language Runtime の略です。 CLR トリガーは、.NET テクノロジ内のほとんどが CLR に基づいて構築されたトリガーの固有のサブセットです。これらのトリガーは、トリガーが多くの計算を実行する必要がある場合、または SQL 以外のエンティティに関連付ける必要がある場合に役立ちます。

DML および DDL トリガーは、Visual Basic、C#、および F-sharp を含む .NET テクノロジでサポートされている CLR トリガーのコーディングを有効にすることによって実際に構築できます。

サンプル SQL Server トリガー

これらのトリガーの概念を例で理解しましょう。

まず、SQL ステートメントを使用してデータベースを作成しましょう。

 CREATE DATABASE testdb; use testdb;

ここでは、データベースの名前として「testdb」を指定しました。そして次のステップは、テーブルを作成することです。

 CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );

学生の詳細を保存するためのテーブルを作成しました。そして、これがテーブルの構造を記述するコマンドです。ここで、「学生」は私が付けたテーブル名です。

 DESC student;

以下は、私が作成したテーブルの構造です。

 +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | id | int | YES | | NULL | | | maths | int | YES | | NULL | | | physics | int | YES | | NULL | | | biology | int | YES | | NULL | | | social | int | YES | | NULL | | | total | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)

テーブルを作成したら、次のステップはトリガーを設定することです。 BEFORE INSERT引数を使用してみましょう。

作成したトリガーの名前は「マーク」です。テーブルが学生の成績で変更されるとすぐに、以下のトリガーが学生の全体的な成績を自動的に決定しようとします。

 CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;

古いデータを使用するのではなく、行データを置き換える必要があるため、新しいクラス名を使用して「total」を定義し、その後のすべての式には、ドット演算子を使用して total の後に新しいキーワードを前に付けます。次に、各行に値を追加して結果を確認します。最初は、すべての生徒の合計点は 0 です。

 INSERT INTO student VALUES("George",02,99,87,92,91,0); INSERT INTO student VALUES("James",03,91,81,94,90,0); INSERT INTO student VALUES("Harry",04,86,70,73,88,0); INSERT INTO student VALUES("John",05,73,89,78,92,0); INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);

この場合、学生テーブルにデータが挿入されると、トリガーステートメントが自動的にトリガーされます。各生徒の合計点は、トリガーによって計算されます。次に、トリガーがSELECTステートメントを使用して呼び出されるかどうかを見てみましょう。

 SELECT * FROM table_name;

そして、これが最終的な出力です。

 mysql> select * from student; +--------+------+-------+---------+---------+--------+-------+ | name | id | maths | physics | biology | social | total | +--------+------+-------+---------+---------+--------+-------+ | George | 2 | 91 | 81 | 94 | 90 | 356 | | James | 3 | 86 | 70 | 73 | 88 | 317 | | Harry | 4 | 73 | 89 | 78 | 92 | 332 | | John | 5 | 94 | 75 | 69 | 79 | 317 | | Lisa | 1 | 99 | 87 | 92 | 91 | 369 | +--------+------+-------+---------+---------+--------+-------+ 5 rows in set (0.00 sec)

上記の結果では、生徒ごとにすべての科目のマークが自動的に追加されていることがわかります。したがって、トリガーが正常に呼び出されたと結論付けることができます。

追加のトリガー操作

トリガーを使用して多くの操作を実行できます。単純なものもあれば、少し複雑なものもあるかもしれませんが、クエリを確認すると、簡単に理解できます。 Transact-SQL ステートメントを使用すると、次のコマンドを使用してトリガーを有効化、無効化、または削除できます。

特定のトリガーが存在するかどうかを確認するクエリ

このコマンドは、データベース全体で指定されたトリガーをチェックします。

 SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'

トリガーを表示するクエリ

アクティブなデータベースで使用可能なすべてのトリガーは、次のステートメントによって表示されます。

 SHOW TRIGGERS;

トリガーを無効にするクエリ

以下のコマンドは、作業データベースのトリガーを無効にします。

 DISABLE TRIGGER trigger_name ON DATABASE;

特定のテーブル名を指定して、トリガーを無効にすることもできます。

 DISABLE TRIGGER trigger_name ON table_name;

トリガーを有効にするクエリ

次のコマンドは、アクティブなデータベース内の指定されたテーブルで定義された特定のトリガーを非アクティブ化してから、再度有効化します。

 ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name

トリガーを有効にする前に無効にする必要があります。

テーブル内のすべてのトリガーを有効または無効にするクエリ

上記の SQL ステートメントを使用すると、特定のトリガー名の代わりに「ALL」を代入することで、一度にすべてのテーブルトリガーを非アクティブ化またはアクティブ化できます。

 ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL

トリガーを削除または削除するクエリ

トリガーは、それを削除するか、テーブル全体を削除することで削除できます。テーブルが削除されると、関連するすべてのトリガーも削除されます。

 DROP TRIGGER [trigger_name];

トリガーが削除されるたびに、関連するデータが sys.objects データテーブルから削除されます。

トリガーの利点

トリガーの作成は簡単で、トリガー自体が格納された関数とメソッドを呼び出すことができます。
ユーザーは、トリガーを使用して簡単な監査を実装できます。
残念なことに、トリガーを使用して制約の操作をエミュレートすることはできますが、SQL Server を使用してデータベースシステム内のエンティティ間で制約を作成することはできません。
整合性制約は、トリガーを使用してデータベース全体に実装できます。
新しく入力または変更されたデータの行ごとの検証ではなく、グループの検証が必要な場合は、トリガーが役立ちます。

トリガーのデメリット

SQL トリガーは、制限があるため、状況によっては最適な選択ではない場合があります。

トリガーは正確に文書化する必要があります。
アプリケーションコンポーネントにアクセスできない可能性があるデータベースの同時実行が原因で、トリガーのデバッグが困難になる場合があります。
トリガーを使用すると、DML ステートメントはより複雑になります。
マイナーなトリガーの問題でさえ、ステートメントに論理的な障害を引き起こす可能性があります。

結論

トリガーは、Transact-SQL と SQL の非常に役立つコンポーネントであり、Oracle でも使用できます。ストアドメソッドを呼び出す場合、トリガーの使用は非常に重要です。これらの SQL トリガーにより、アクティビティのタイムラインを分析し、必要に応じてそれらに対応する方法を決定できます。データを取得するトリガーに接続されている特定のテーブルを確認することもできます。

再帰はトリガーによって有効にできます。テーブルのトリガーが親テーブルのコマンドを実行するたびに、トリガーの 2 回目の反復がオフに設定されます。これは再帰トリガーと呼ばれます。これは、同一性相関を解決しようとする際に役立ちます。

さらに、トリガーは、データベースが受け入れることを許可されている更新パターンを規制します。主に主キーと外部キーの SQL 制約キーが存在しない場合、データベースシステムでデータ整合性制約を維持することは非常に有益です。

この記事が、SQL トリガーについて学ぶのに役立つことを願っています。

データベースについて深く学びたい場合は、SQL と NoSQL を学ぶための優れたリソースをいくつか紹介します。

SQL トリガー: エッセンシャル ガイド