トリガーの作成と編集
SQL プロシージャの作成と編集 への移動
目次
トリガーは、テーブルに関連付けられたコード オブジェクトです。SQL の INSERT、UPDATE、または DELETE 文によってテーブルが変更される際に、自動的に実行されます。トリガーには複雑な SQL 文を含めることができます。たとえば、他のテーブルのクエリーに必要な文などです。ただし、パラメータや引数を受け取ることはできず、操作のコミットやロールバックは実行できません。他のスクリプトやアプリケーションと同じく、コード作成上のエラーが発生する可能性もあります。
トリガーはカスタマイズ可能であり、自動的に実行されるため、次のような用途に使用されます。
- 参照整合性ルールを実装する。たとえば、［顧客］テーブルに東部地域の新しい顧客を追加する際は、その地域を担当する販売員のために、新しい顧客に関する情報もテーブルに追加する必要があります。
- テーブル データにビジネス ルールを実装する。たとえば、顧客のアカウントの状態に基づいて、新規注文を制限します。顧客の支払いが期限を過ぎているか、または注文が顧客のクレジット限度額を超えている場合、注文は拒否されます。
- テーブルの更新時に他のテーブルの情報をシンクロする。たとえば、［製品］テーブルに Delete トリガーを作成し、［売上］テーブルと［未決注文］テーブルから一致する行を特定して同時に削除することができます。
- 更新の発生時に SQL サーバー外部のアクションを開始する。たとえば、新入社員を［従業員］テーブルに追加したときに、人事部に電子メールが送信され、その社員の席やコンピュータ機器などを手配するように依頼します。さらに、事務処理に必要なすべての文書が新入社員に送付され、記入済みの書類が人事部に返送されるまでの手続きが確実に行われるようにします。
- UPDATE などの特定の操作が実行されないようにする。たとえば、週の勤務時間が 16 時間未満の従業員（［人物］テーブルに保存）に賞与（［給与］テーブルに保存）を適用しようとしたときに、トリガーを使用して更新をロールバックできます。
モデルに関連付けられたトリガーは、モデル エクスプローラの［トリガー］ノードに表示されます。ただし、トリガーを作成するには、トリガーを関連付けるテーブルを選択して、［テーブル エディタ］の［依存関係］タブを使用する必要があります。
生成されたトリガー コードは、対象データベースに応じてカスタマイズされます。ER/Studio Data Architect では、トリガー コードの生成にテンプレートが使用されます。トリガーは、特別なタイプの SQL プロシージャであると言えます。
ER/Studio Data Architect で使用できるトリガーの種類は次のとおりです。
- 参照整合性トリガー。データベースに矛盾点が作成されるのを防ぎ、データベース内の暗黙的なリレーションシップを実装します。また、手続き型 SQL コマンドを管理および処理する方法をデータベースに通知して、組織のビジネス ルールを実装します。参照整合性トリガーは、親テーブルと子テーブルの間に挿入、更新、および削除オプションを設定することで定義されます。
［リレーションシップ エディタ］の［トリガー］タブで、参照整合性のレベルと、SQL の挿入、更新、および削除時の動作を定義できます。詳細は、「参照整合性トリガーの作成」を参照してください。
- ユーザー定義トリガー。通常、特別な状況に対してカスタマイズされ、データベースのデフォルト機能によって生成されるコードを上書きします。ユーザー定義トリガーは DBMS 固有のコードと密接に結びついているため、物理モデルのみで実装されます。ユーザー定義トリガーには次の 3 種類があります。
- スクリプト トリガー。Oracle の PL/SQL や Microsoft の Transact-SQL など、データベースの手続き型コードで直接記述されます。データベース上にスクリプト トリガーが存在する場合、リバース エンジニアリングを実行できます。スクリプト トリガーは、モデル ウィンドウに表示することもできます。リバース エンジニアリングされたトリガーには、テーブル名や他のオブジェクト参照がコード内に直接記述されています。詳細は、「ユーザー定義スクリプト トリガーの作成」を参照してください。
- テンプレート トリガー。トリガーが参照する特定のオブジェクト名（テーブル名や特定のカラム）がコード内に記述されません。これらのトリガーはアプリケーションのさまざまなオブジェクトで再利用することができ、トリガー コードの本体部はモデル全体で再利用されます。詳細は、「ユーザー定義テンプレート トリガーの作成」を参照してください。
- 再利用可能なトリガー。任意のテーブルに対してアクションを実行できます。詳細は、「ユーザー定義の再利用可能なトリガーの作成」を参照してください。
トリガーの例
Sub Main
Dim trigName As String
Dim trigBody As String
Dim attrib As AttributeObj
Dim crlf As String
crlf = Chr(13) + Chr(10)
trigName = Left(CurrEntity.TableName, 24) + "UpdTrg"
trigBody = "CREATE OR REPLACE TRIGGER " + trigName + " AFTER UPDATE ON " + CurrEntity.TableName + crlf
trigBody = trigBody + "REFERENCING OLD AS OLD NEW AS NEW" + crlf
trigBody = trigBody + "FOR EACH ROW" + crlf
trigBody = trigBody + "BEGIN" + crlf
trigBody = trigBody + "UPDATE " + CurrEntity.TableName + " SET UPDATEDATE = SYSDATE WHERE "
For Each attrib In CurrEntity.Attributes
If attrib.PrimaryKey = True Then
If Right(trigBody,6) <> "WHERE " Then
trigBody = trigBody + " AND "
End If
trigBody = trigBody + attrib.ColumnName + " =:OLD." + attrib.ColumnName
End If
Next attrib
trigBody = trigBody + ";" + crlf + "END;"
' resultstring は、データベースの生成ウィザードを実行したときにトリガーを DDL スクリプトに出力します。
' トリガー DDL の生成に使用される文字列変数を、resultstring に設定する必要があります。
resultstring = trigBody
' このメッセージ ボックスを使用して、VB コードのデバッグ時に SQL を表示します。テーブルを選択する必要があります。
MsgBox(trigBody)
End Sub
トリガーは、次のデータベース プラットフォームでサポートされます。
- IBM DB2 for LUW 5.x、6.x、7.x、8.x、9.x、および 10.x
- IBM DB2 for OS/390 6.x および IBM DB2 for z/OS 7.x、8.x、9.x、および 10.x
- InterBase 2009、XE、XE3
- Microsoft SQL Server 4.x、6.x、7.x、2000 ～ 2014
- Oracle 7.x、8.x、9i、10g、11g、および 12c
- Sybase ASA 6.0、7.0、8.0、9.0
- Sybase Adaptive Server IQ 12
- Sybase SQL Server System ASE 11.0、11.5、11.9、12.0、12.5、15
参照整合性トリガーの作成
参照整合性トリガー（システム トリガーとも呼びます）は、データベースに矛盾点が作成されるのを防ぎ、データベース内の暗黙的なリレーションシップを実装します。また、手続き型 SQL コマンドを管理および処理する方法をデータベースに通知して、組織のビジネス ルールを実装します。参照整合性トリガーを定義するには、［リレーションシップ エディタ］の［トリガー］タブで、親テーブルと子テーブルに対する挿入、更新、および削除時のアクションを設定します。
メモ： 多対多リレーションシップには参照整合性トリガーを作成できません。
- モデル ウィンドウで、2 つのテーブル間にリレーションシップを作成します。これらの一方のテーブルで SQL の INSERT、UPDATE、または DELETE 操作が実行されたときに、もう一方のテーブルを更新する必要があるとします。
- モデル ウィンドウで、作成したリレーションシップをダブルクリックします。
- ［リレーションシップ エディタ］の［トリガー］タブをクリックします。
次に、一部のオプションについて補足説明します。
- トリガーやプロシージャのようなプロシージャ ロジックのコードを生成するには、トリガーの結果を resultstring という変数に格納する必要があります。たとえば、"Northwind.dm1" サンプル モデルには、データ ディクショナリに Oracle プラットフォーム用の再利用可能なトリガー、SYSUPDATE があります。トリガーのコードには次の文が含まれています。これはトリガーのコード生成に必要です。
resultstring = trigBody - ER/Studio Data Architect では、参照整合性トリガーのコード生成にテンプレートが使用されます。生成されたトリガー コードは、対象データベースに応じてカスタマイズされます。必要なデータ修正の種類、選択、およびレベルに基づき、挿入、更新、削除の各アクションに対して、次のトリガー テンプレートを適用できます。
- ［親アクション］： 親が更新される時の子の動作を定義します。
- ［挿入: None］： この設定は変更できません。
- ［None］（アクションなし）： トリガーは、外部キー値が親テーブルの主キーに存在するかを確認します。値が確認できない場合、更新または削除を実行すると参照整合性のチェックに失敗します。
- ［Set Null］： トリガーは、外部キー値が親テーブルの主キーに存在するかを確認します。値が確認できない場合、子テーブルの外部キー値を NULL に設定してから、親の更新または削除を実行します。たとえば、親テーブル［人物］と子テーブル［タスク］との間に、ID を共有する必須リレーションシップがあるとします。Set Null トリガーを使用すると、対応する ID 値が［人物］テーブルに無くても、その ID を含む［タスク］テーブルにデータを挿入できます。この場合、データベースは参照整合性エラーをスローしません。
- ［Set Default］： 外部キーにデフォルト値を設定してから、親の更新または削除を実行します。
- ［Restrict］： トリガーは、外部キー値が親テーブルの主キーに存在するかを確認します。値が確認できない場合、データの挿入、更新、または削除が禁止されます。たとえば、親テーブル［人物］と子テーブル［タスク］との間に、ID を共有する必須リレーションシップがあるとします。Restrict トリガーを使用すると、対応する ID 値が［人物］テーブルに無いときは、その ID を含む［タスク］テーブルへのデータ挿入を禁止します。この場合、データベースは参照整合性エラーをスローします。
- ［Cascade］： 親テーブルの主キーが更新または削除されると、子テーブルの対応する外部キー値にその変更が継承されます。
ユーザー定義スクリプト トリガーの作成
- モデル ウィンドウまたはモデル エクスプローラで、トリガーを作成するテーブルを選択します。
- ［編集｜テーブルの編集］ を選択します。
- ヒント： 代わりに、モデル ウィンドウでテーブルをダブルクリックするか、テーブルを右クリックしてショートカット メニューの［テーブルの編集］をクリックし、［テーブル エディタ］を開くこともできます。
- ［テーブル エディタ］の［依存関係］タブをクリックします。
- ［追加］をクリックして、［トリガー｜スクリプト］をクリックします。
- ［トリガー エディタ］で必要な設定を完了したら、［OK］をクリックするとトリガーが作成されます。
以下のセクションでは、［トリガー エディタ］のオプションの設定について説明します。
メモ
- DDL生成ウィザードでデータベースを生成する際に、DDL に含めるトリガーや、トリガーの生成に CREATE または DROP 文のどちらを使用するかを選択できます。［全般］タブでは、参照整合性トリガーなどのシステム トリガーの作成を選択できます。
- トリガーは、モデル エクスプローラの物理モデル内に次の形式で表示されます。
トリガー名.関連付けられたテーブル名 - トリガー アクションを表示するには、［ダイアグラムとオブジェクトの表示オプション］ダイアログ ボックスの［リレーションシップ］タブで、［トリガー アクション］を選択します。トリガー アクションの表示を有効にすると、たとえば、［更新］（Update）が［Restrict］に設定された［従業員］テーブルの横には「U:R」と表示されます。
- 一部のコマンド、文、および操作は、トリガー プログラムで使用できません。トリガーでそのような例外項目を生成しないようにするには、ご使用の DBMS のマニュアルを参照してください。
- トリガーやプロシージャのようなプロシージャ ロジックのコードを生成するには、トリガーの結果を resultstring という変数に格納する必要があります。たとえば、"Northwind.dm1" サンプル モデルには、データ ディクショナリに Oracle プラットフォーム用の再利用可能なトリガー、SYSUPDATE があります。トリガーのコードには次の文が含まれています。これはトリガーのコード生成に必要です。
resultstring = trigBody - ［トリガー エディタ］には、SQL テキスト文字列の編集に使用できる検索置換機能があります。
- ［名前］： トリガー名を入力するか、既存のトリガー名を編集します。
［SQL］タブ
トリガーの CREATE TRIGGER 文を入力するか、既存のスクリプトを編集できます。ここで SQL スクリプトを入力する代わりに、［インポート］をクリックしてスクリプトをインポートすることもできます。
- ［エクスポート］： SQL スクリプトを *.sql ファイルにエクスポートします。この機能は、現在作成しているトリガーを基にして追加のトリガーを作成する場合に役立ちます。コードをエクスポートして、後で別のトリガーにインポートできます。［エクスポート］をクリックすると、［名前を付けて保存］ダイアログ ボックスが開きます。ファイル名を入力してください。ファイルは［保存する場所］ボックスに表示されているフォルダに保存されます。別のフォルダに保存するには、フォルダを参照して指定します。
- ［インポート］： *.sql ファイルをインポートします。［インポート］をクリックすると、［ファイルを開く］ダイアログ ボックスが開きます。*.sql ファイルの名前を入力するか、参照して指定します。
- ［検証］： SQL スクリプトを検証します。エラーが検出された場合は、メッセージ ボックスに表示されます。エラー メッセージには、エラーの種類、行番号、およびカラム情報が含まれます。
［説明］タブ
テキスト ボックスにトリガーの説明を入力できます。
［アタッチメントのバインド］タブ
アタッチメント（外部情報）をトリガーにバインドします。また、オブジェクトからアタッチメントを削除したり、アタッチメントのバインドのデフォルト値を上書きしたり、バインドされたアタッチメントの位置を変更することもできます。選択したアタッチメントのグリッド（画面の右側）に移動したアタッチメントの値をオーバーライドするには、対象となるアタッチメントの［値］フィールドをダブルクリックします。アタッチメントのデータ型に応じて、［上書き値エディタ］またはリスト ボックスが表示されます。アタッチメントは、［データ ディクショナリ］タブの［アタッチメント］フォルダに作成されます。詳細は、「データ モデルへの外部ドキュメントの関連付け」を参照してください。
ユーザー定義テンプレート トリガーの作成
- モデル エクスプローラで、物理［メイン モデル］を展開します。［トリガー］ノードを右クリックして、ショートカット メニューの［トリガーの作成］をクリックします。
- ［トリガー エディタ］で必要な設定を完了したら、［OK］をクリックするとトリガーが作成されます。
ヒント： 作成したトリガーを編集するには、トリガーを右クリックして、ショートカット メニューの［トリガーの編集］をクリックします。
以下では、［トリガー エディタ］のオプションの設定について説明します。
［説明］タブ
トリガーの定義を入力または編集します。対象データベースでサポートされる場合、SQL コードの生成時に、この定義がコメントとして追加されます。
［アタッチメントのバインド］タブ
アタッチメント（外部情報）をトリガーにバインドします。また、オブジェクトからアタッチメントを削除したり、アタッチメントのバインドのデフォルト値を上書きしたり、バインドされたアタッチメントの位置を変更することもできます。選択したアタッチメントのグリッド（画面の右側）に移動したアタッチメントの値をオーバーライドするには、対象となるアタッチメントの［値］フィールドをダブルクリックします。アタッチメントのデータ型に応じて、［上書き値エディタ］またはリスト ボックスが表示されます。アタッチメントは、［データ ディクショナリ］タブの［アタッチメント］フォルダに作成されます。詳細は、「データ モデルへの外部ドキュメントの関連付け」を参照してください。
ユーザー定義の再利用可能なトリガーの作成
再利用可能なユーザー定義トリガーは、データ ディクショナリで作成されます。詳細は、「再利用可能なトリガーの作成と編集」を参照してください。
メモ
- ［トリガー エディタ］には、SQL テキスト文字列の編集に使用できる検索置換機能があります。
- トリガーやプロシージャのようなプロシージャ ロジックのコードを生成するには、トリガーの結果を resultstring という変数に格納する必要があります。たとえば、"Northwind.dm1" サンプル モデルには、データ ディクショナリに Oracle プラットフォーム用の再利用可能なトリガー、SYSUPDATE があります。トリガーのコードには次の文が含まれています。これはトリガーのコード生成に必要です。
resultstring = trigBody