データ整合性を設定するのは、データベース中のデータの品質を保証するためです。テーブルを設計するときの2つの重要なステップは、列に対する有効な値を明確にすることと、その列データの整合性の設定方法を決定することです。データ整合性は4種類に分類され、その設定方法も何通りかあります。
| 整合性の種類 | 設定方法 |
| 実体の整合性 | PRIMARY KEY （主キー）制約
UNIQUE （一意）制約 IDENTITY プロパティ |
| ドメインの整合性 | ドメイン DEFAULT 定義
FOREIGN KEY （外部キー）制約 CHECK （チェック）制約 NULL 値を許容するかどうか |
| 参照整合性 | FOREIGN KEY （外部キー）制約
CHECK （チェック）制約 Oracle の CASCADE および DELETE 機能を実装するトリガ |
| ユーザー定義の整合性 | CREATE TABLE 中の列およびテーブル レベルの全制約
ストアド プロシージャ トリガ |
実体の整合性
実体の整合性とは、特定のテーブルの行を一意の実体として定義することです。実体の整合性では、テーブルの ID 列または主キーの整合性をインデックス、UNIQUE 制約、PRIMARY KEY 制約、IDENTITY プロパティをとおして設定します。
制約の名前付け
制約には必ず明示的に名前を付ける必要があります。名前を指定しなかった場合、Oracle と Microsoft SQL Server は異なる名前付け規則に従って制約に暗黙的に名前を付けます。そうした名前の違いによって移行プロセスが不必要に煩雑になることがあります。名前の違いが表面化するのは、制約を削除または使用不可にするときです。制約の削除には名前を指定しなければならないからです。制約に明示的に名前を付ける構文は、Oracle と SQL Server で同じです。
CONSTRAINT constraint_name
主キーと一意な列
SQL-92 標準の下では、主キーの値はすべて一意でなければならず、その列には NULL 値が許容されません。Oracle と Microsoft SQL Server は共に、PRIMARY KEY または UNIQUE 制約が定義されているときはいつでも一意のインデックスを自動的に作成するという形で一意性を設定します。また、主キー列は自動的に NOT NULL として定義されます。主キーはテーブルに1個だけ認められます。
SQL Server では特に指定しない限り、主キーとしてクラスタ化インデックスが作成されますが、非クラスタ化インデックスを指定することもできます。Oracle では主キーに対するインデックスを削除するには制約を削除または使用不可にすればいいのですが、SQL Server のインデックスを削除するには制約を削除するしか方法がありません。
どちらの RDBMS でも、UNIQUE ステートメントで代替キーを定義することができます。テーブルにはUNIQUE 制約を複数定義することができます。UNIQUE 制約の設定された列は、NULL 値が許容されます。SQL Server では特に指定しない限り、非クラスタ化インデックスが作成されます。
アプリケーションの移行に際しては、SQL Server では一意のキー全体 (1 つまたは複数の列インデックス) で NULL 値が許容されるのは1行だけであるのに対し、Oracle では一意のキー全体でNULL 値を含む行は何行あってもいいことに注意する必要があります。
| Oracle | Microsoft SQL Server |
| CREATE TABLE DEPT_ADMIN.DEPT (DEPT VARCHAR2(4) NOT NULL, DNAME VARCHAR2(30) NOT NULL, CONSTRAINT DEPT_DEPT_PK PRIMARY KEY (DEPT) USING INDEX TABLESPACE USER_DATA PCTFREE 0 STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED), CONSTRAINT DEPT_DNAME_UNIQUE UNIQUE (DNAME) USING INDEX TABLESPACE USER_DATA PCTFREE 0 STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED) ) |
CREATE TABLE USER_DB.DEPT_ADMIN.DEPT (DEPTVARCHAR(4) NOT NULL, DNAMEVARCHAR(30) NOT NULL, CONSTRAINT DEPT_DEPT_PK PRIMARY KEY CLUSTERED (DEPT), CONSTRAINT DEPT_DNAME_UNIQUE UNIQUE NONCLUSTERED (DNAME) ) |
制約の追加と削除
制約を使用不可にすると、データベースのパフォーマンスが改善され、データ レプリケーション処理が合理化されます。たとえば、リモート サイトでテーブル データを再作成またはレプリケーションするときには、テーブルへの初期データ入力時にデータの整合性が検査されているので、制約検査を重ねて実施する必要がなくなります。Oracle アプリケーションは制約を使用不可または使用可能にできるような形にプログラムすることができます (PRIMARY KEY と UNIQUE は例外)。レプリケーション時には、NOT FOR REPLICATION 句を使用して列レベル、外部キー、CHECKによる制約を抑制することをお勧めします。
データのレプリケーションを行わないが、制約を削除する必要があるという場合は、Microsoft SQL Server では ALTER TABLE ステートメントに CHECK および WITH NOCHECK オプションを使用すれば目的を達成できます。
下記の図はこのプロセスを比較したものです。
.gif)
SQL Server では、NOCHECK 句に ALL キーワードを使用すればテーブルの制約をすべて遅延することができます。
Oracle アプリケーションで PRIMARY KEY または UNIQUE 制約を使用不可または削除するために CASCADE オプションが使用されている場合は、コードを多少書き直す必要があるかもしれません。CASCADE オプションはその親とそれに関連する子の整合性に対する制約を使用不可または削除するからです。
下記に示すのはその構文例です。
DROP CONSTRAINT DEPT_DEPT_PK CASCADE
SQL Server ベースのアプリケーションの変更では、まず子の制約を削除してから、親の制約を削除しなければなりません。たとえば、DEPT テーブルに対する PRIMARY KEY 制約を削除するには、列 STUDENT.MAJOR と CLASS.DEPT の外部キーを削除しなければなりません。下記に示すのはその構文例です。
ALTER TABLE STUDENT
DROP CONSTRAINT STUDENT_MAJOR_FK
ALTER TABLE CLASS
DROP CONSTRAINT CLASS_DEPT_FK
ALTER TABLE DEPT
DROP CONSTRAINT DEPT_DEPT_PK
制約の追加と削除に使用する ALTER TABLE 構文は、Oracle と SQL Server でほとんど同じです。
連続的な数値の生成
Oracle アプリケーションで SEQUENCE が使用されている場合は、Microsoft SQL Server の IDENTITY プロパティを利用するように変更するのは簡単です。
| カテゴリ | Microsoft SQL Server の IDENTITY |
| 構文 | CREATE TABLE new_employees ( Empid int IDENTITY (1,1), Employee_Name varchar(60), CONSTRAINT Emp_PK PRIMARY KEY (Empid) ) インクリメント間隔が 5 のとき: CREATE TABLE new_employees ( Empid int IDENTITY (1,5), Employee_Name varchar(60), CONSTRAINT Emp_PK PRIMARY KEY (Empid) ) |
| テーブル当たりの ID 列の個数 | 1 つ |
| 許容される NULL 値の個数 | なし |
| 特に指定しないときの制約の使用、値 | 使用できない |
| 一意性の設定 | 可能 |
| INSERT、SELECT INTO、一括コピーの終了後に現在の ID 値の最大値をクエリ。 | @@IDENTITY (function) |
| ID 列の作成時に指定したシード値を戻す。 | IDENT_SEED('table_name') |
| ID 列の作成時に指定したインクリメント値を戻す。 | IDENT_INCR('table_name') |
| SELECT 構文 | キーワード IDENTITYCOL は、SELECT、INSERT、UPDATE、DELETE ステートメントにおいて IDENTITY プロパティが指定されている列を参照するときに、列名の代わりに使用することができます。 |
IDENTITY プロパティは 1 つのテーブル中での行に自動的に番号を付けますが、それぞれ専用の ID 列がある独立した複数のテーブルについてみれば、同じ値が付けられることがあります。これは IDENTITY プロパティが、それが使用されているテーブルに関してのみ一意性を保証するものであるからです。アプリケーションで作成する ID 列が、データベース全体あるいはでネットワークでつながれた世界中のすべてのコンピュータのすべてのデータベースで一意である必要がある場合は、ROWGUIDCOL プロパティ、uniqueidentifier 型、NEWID 関数を使用することです。SQL Server ではマージ レプリケーション時にはグローバルに一意な ID 列を使用して、行がテーブルの複数コピーの間で一意に識別されるようにすることです。
ID 列の作成と変更についての詳細は、SQL Server Books Online を参照してください。
ドメイン整合性
ドメイン整合性とは、特定の列に対して有効なエントリを設定することです。ドメインの整合性は、データの種類 (データ型による)、フォーマット (CHECK 制約による)、取り得る値の範囲 (REFERENCE および CHECK 制約による) を制限することで設定されます。
DEFAULT および CHECK 制約
Oracle ではデフォルトは列プロパティとして扱ほれますが、Microsoft SQL Server ではデフォルトは制約として扱われます。SQL Server の DEFAULT 制約には定数、引数を受け取らない組み込み関数 (niladic 関数)、NULL のいずれかを含めることができます。
Oracle の DEFAULT 列プロパティの移行を容易にするためには、SQL Server の列レベルで制約名を適用せずに DEFAULT 制約を定義すべきです。各 DEFAULT 制約ごとに一意の名前が SQL Server によって生成されます。
CHECK 制約を定義するための構文は、Oracle と SQL Server で同じです。検索条件は論理式に評価されなければならず、サブクエリを含むことができません。列レベルの CHECK 制約は制約が設定されている列だけを参照することができます。CHECK 制約は 1 つのテーブルに複数定義することができます。SQL Server の構文では CREATE TABLE ステートメント中の列に対して列レベルの CHECK 制約を 1 つだけ作成することができ、その制約は複数の条件をもつことができます。
修正した CREATE TABLE ステートメントをテストする最善の方法は、SQL Server で SQL Server クエリ アナライザ を使用することです。エラーがあれば結果ペインに表示されます。制約構文についての詳細は、SQL Server Books Online を参照してください。
| Oracle | Microsoft SQL Server |
| CREATE TABLE STUDENT_ADMIN.STUDENT ( SSN CHAR(9) NOT NULL, FNAME VARCHAR2(12) NULL, LNAME VARCHAR2(20) NOT NULL, GENDER CHAR(1) NOT NULL CONSTRAINT STUDENT_GENDER_CK CHECK (GENDER IN ('M','F')), MAJOR VARCHAR2(4) DEFAULT 'Undc' NOT NULL, BIRTH_DATE DATE NULL, TUITION_PAID NUMBER(12,2) NULL, TUITION_TOTAL NUMBER(12,2) NULL, START_DATE DATE NULL, GRAD_DATE DATE NULL, LOAN_AMOUNT NUMBER(12,2) NULL, DEGREE_PROGRAM CHAR(1) DEFAULT 'U' NOT NULL CONSTRAINT STUDENT_DEGREE_CK CHECK (DEGREE_PROGRAM IN ('U', 'M', 'P', 'D')), ... |
CREATE TABLE USER_DB.STUDENT _ADMIN.STUDENT ( SSN CHAR(9) NOT NULL, FNAME VARCHAR(12) NULL, LNAME VARCHAR(20) NOT NULL, GENDER CHAR(1) NOT NULL CONSTRAINT STUDENT_GENDER_CK CHECK (GENDER IN ('M','F')), MAJOR VARCHAR(4) DEFAULT 'Undc' NOT NULL, BIRTH_DATE DATETIME NULL, TUITION_PAID NUMERIC(12,2) NULL, TUITION_TOTAL NUMERIC(12,2) NULL, START_DATE DATETIME NULL, GRAD_DATE DATETIME NULL, LOAN_AMOUNT NUMERIC(12,2) NULL, DEGREE_PROGRAM CHAR(1) DEFAULT 'U' NOT NULL CONSTRAINT STUDENT_DEGREE_CK CHECK (DEGREE_PROGRAM IN ('U', 'M', 'P', 'D')), ... |
ユーザー定義のルールとデフォルトについて述べておきます。Microsoft SQL Server のルールとデフォルトの構文は後方互換性の維持するために残されていますが、新しいアプリケーションの開発には CHECK 制約と DEFAULT 制約を使用することが望ましくなります。詳細については、SQL Server Books Online を参照してください。
NULL 値を許容するかどうか
Microsoft SQL Server と Oracle は NULL 値を許容するかどうかを設定するために列制約を作成します。Oracle の列は CREATE TABLE または ALTER TABLE ステートメントで NOT NULL を指定しない限り、NULL 値になります。Microsoft SQL Server では、データベースおよびセッション設定は列定義で使用したデータ型の NULL 値を使用するかどうかの設定を無効にすることができます。
SQL スクリプト (Oracle または SQL Server の区別なく) ではすべて各列に明示的に NULL と NOT NULL を定義すべきです。これがどのような形で実装されているかは、テーブル作成スクリプトのサンプル、Oratable.sql と Sstable.sql を参考にしてください。明示的な指定がないとき、列に NULL 値を許容するかどうかは次のような規則に従います。
| Null の設定 | 説明 |
| 列がユーザー定義型で定義されている。 | Microsoft SQL Server はデータ型の作成時に指定された NULL 値を許容するかどうかの設定を使用する。データ型の NULL 値を許容するかどうかのデフォルトを取り出すにはシステム ストアド プロシージャ sp_help を使用する。 |
| 列がシステム供給のデータ型で定義されている。 | システム供給のデータ型にオプションが 1 つしかない場合、それが優先される。現在、bit型は NOT NULL としてのみ定義できる。 セッション設定のどれかが ONであるとき ( SET を使用して設定): ANSI_NULL_DFLT_ON が ON なら、NULL が割り当てられる。 ANSI_NULL_DFLT_OFF が ON なら、NOT NULL が割り当てられる。 データベース設定のどれかが設定されているとき (システム ストアド プロシージャ sp_dboption で変更): ANSI NULL デフォルトが true なら、NULL が割り当てられる。 ANSI NULL デフォルトが false なら、NOT NULL が割り当てられる。 |
| NULL/NOT NULL 未定義 |
明示的に定義されていない (ANSI_NULL_DFLT オプションがどちらも設定されていない) とき、セッションには変更がなく、データベースはデフォルトに設定されているので (ANSI null default is false)、SQL Server はそれに NOT NULL を割り当てます。 |
参照整合性
次の表は参照整合性を設定する制約を定義する構文を比較したものです。
| Constraint | Oracle | Microsoft SQL Server |
| PRIMARY KEY | [CONSTRAINT constraint_name] PRIMARY KEY (col_name [, col_name2 [..., col_name16]]) [USING INDEX storage_parameters] |
[CONSTRAINT constraint_name] PRIMARY KEY [CLUSTERED | NONCLUSTERED] (col_name [, col_name2 [..., col_name16]]) [ON segment_name] [NOT FOR REPLICATION] |
| UNIQUE | [CONSTRAINT constraint_name] UNIQUE (col_name [, col_name2 [..., col_name16]]) [USING INDEX storage_parameters] |
[CONSTRAINT constraint_name] UNIQUE [CLUSTERED | NONCLUSTERED](col_name [, col_name2 [..., col_name16]]) [ON segment_name] [NOT FOR REPLICATION] |
| FOREIGN KEY | [CONSTRAINT constraint_name] [FOREIGN KEY (col_name [, col_name2 [..., col_name16]])] REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])] [ON DELETE CASCADE] |
[CONSTRAINT constraint_name] [FOREIGN KEY (col_name [, col_name2 [..., col_name16]])] REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])] [NOT FOR REPLICATION] |
| DEFAULT | 列プロパティであり、制約ではない DEFAULT (constant_expression) |
[CONSTRAINT constraint_name] DEFAULT {constant_expression | niladic-function | NULL} [FOR col_name] [NOT FOR REPLICATION] |
| CHECK | [CONSTRAINT constraint_name] CHECK (expression) |
[CONSTRAINT constraint_name] CHECK [NOT FOR REPLICATION] (expression) |
NOT FOR REPLICATION 句はレプリケーション時に列レベルの FOREIGN KEY 制約と CHECK 制約を抑制するために使用されます。
外部キー
外部キーを定義するための規則はどちらの RDBMS でも似ています。外部キーに指定された各列の列数とデータ型が REFERENCES 句と一致しなければならないことです。この列に入力された NULL 以外の値は REFERENCES 句で定義されたテーブル中に存在しなければならず、参照されたテーブルの列には PRIMARY KEY または UNIQUE 制約が設定されていなければなりません。
Microsoft SQL Server の制約は、同じデータベース中の複数のテーブルを参照する手段となります。複数のデータベース間に参照整合性を実装するには、テーブル ベースのトリガを使用することです。
Oracle と SQL Server は共に自己参照テーブルをサポートします。これは同じテーブルの 1 つまたは複数の列に対して参照 (外部キー) を配置できるテーブルです。たとえば、CLASS テーブルの列 prereq で、CLASS テーブルの列 ccode を参照すれば、コースの必須条件として有効なコース コードが入力されていることを確認することができます。
連鎖削除と連鎖更新は、Oracle では CASCADE DELETE 句で実装されているのに対し、SQL Server では同じ機能がテーブル トリガによって提供されます。詳細については、本書の後の節「SQL 言語のサポート」を参照してください。
ユーザー定義の整合性
ユーザー定義の整合性とは、ほかの整合性のどれにも該当しないような独自のビジネス ルールを定義するためのものです。
ストアド プロシージャ
Microsoft SQL Server のストアド プロシージャは、CREATE PROCEDURE ステートメントを使用してユーザー供給パラメータの受け渡しをします。一時ストアド プロシージャを除き、ストアド プロシージャはカレント データベース中に作成されます。下記の表は Oracle と SQL Server でのその構文です。
| Oracle | Microsoft SQL Server |
| CREATE OR REPLACE PROCEDURE [user.]procedure [(argument [IN | OUT] datatype [, argument [IN | OUT] datatype] {IS | AS} block |
CREATE PROC[EDURE] procedure_name [;number] [ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [,...] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ] [FOR REPLICATION] AS sql_statement [...] |
SQL Server では、一時プロシージャは tempdb データベースに作成され、procedure_name の先頭にシャープ記号が 1 つ付く (#procedure_name) のがローカル一時プロシージャ、シャープ記号が2つ付く (##procedure_name) がグローバル一時プロシージャです。
ローカル一時プロシージャはその作成者だけが使用できます。ローカル一時プロシージャを実行する権限はほかのユーザーに許可することができません。ローカル一時プロシージャはユーザー セッションの終了時に自動的に削除されます。
グローバル一時プロシージャは SQL Server の全ユーザーに提供されます。グローバル一時プロシージャが作成された場合、全ユーザーがそれにアクセスでき、その権限は明示的に取り消さなければなりません。グローバル一時プロシージャはそのプロシージャを使用する最後のユーザー セッションの終了時に削除されます。
SQL Server のストアド プロシージャは最高 32 レベルまでネストすることができます。ネスト レベルは呼び出されたプロシージャが実行を開始したときにインクリメントされ、呼び出されたプロシージャが実行を終了したときにデクリメントされます。
次の例は Oracle の PL/SQL パッケージ関数の代わりに Transact-SQL ストアド プロシージャを使用する方法を示したものです。この例では Transact-SQL ストアド プロシージャはずっと簡単になっていますが、それは SQL Server では結果セットを直接、ストアド プロシージャ中の SELECT ステートメントから戻すことができ、カーソルを使用する必要がないからです。
| Oracle | Microsoft SQL Server |
| CREATE OR REPLACE PACKAGE STUDENT_ADMIN.P1 AS ROWCOUNT NUMBER :=0; CURSOR C1 RETURN STUDENT%ROWTYPE; FUNCTION SHOW_RELUCTANT_STUDENTS (WORKVAR OUT VARCHAR2) RETURN NUMBER; END P1; / CREATE OR REPLACE PACKAGE BODY STUDENT_ADMIN.P1 AS CURSOR C1 RETURN STUDENT%ROWTYPE IS SELECT * FROM STUDENT_ADMIN.STUDENT WHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE WHERE GRADE.SSN=STUDENT.SSN) ORDER BY SSN; FUNCTION SHOW_RELUCTANT_STUDENTS (WORKVAR OUT VARCHAR2) RETURN NUMBER IS WORKREC STUDENT%ROWTYPE; BEGIN IF NOT C1%ISOPEN THEN OPEN C1; ROWCOUNT :=0; ENDIF; FETCH C1 INTO WORKREC; IF (C1%NOTFOUND) THEN CLOSE C1; ROWCOUNT :=0; ELSE WORKVAR := WORKREC.FNAME||' '||WORKREC.LNAME|| ', social security number '||WORKREC.SSN||' is not enrolled in any classes!'; ROWCOUNT := ROWCOUNT + 1; ENDIF; RETURN(ROWCOUNT); EXCEPTION WHEN OTHERS THEN IF C1%ISOPEN THEN CLOSE C1; ROWCOUNT :=0; ENDIF; RAISE_APPLICATION_ERROR(-20001,SQLERRM); END SHOW_RELUCTANT_STUDENTS; END P1; / |
CREATE PROCEDURE STUDENT_ADMIN.SHOW_RELUCTANT_STUDENTS AS SELECT FNAME+'' +LNAME+', social security number'+ SSN+' is not enrolled in any classes!' FROM STUDENT_ADMIN.STUDENT S WHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE G WHERE G.SSN=S.SSN) ORDER BY SSN RETURN@@ROWCOUNT GO |
SQL Server は Oracle のパッケージまたは関数に類似した構造をサポートせず、ストアド プロシージャを作成するための CREATE OR REPLACE オプションもサポートしません。
ストアド プロシージャの実行の遅延
Microsoft SQL Server にある WAITFOR は、開発者がステートメント ブロック、ストアド プロシージャ、トランザクションを起動する時刻、時間間隔、イベントを指定できるという機能です。これは Oracle の dbms_lock.sleep と等価な Transact-SQL です。
WAITFOR {DELAY 'time' | TIME 'time'}
引数は次のとおりです。
DELAY:
Microsoft SQL Serverを指定の時間が経過するまで待ち状態にします。最大値は 24 時間です。
'time':
待機する時間です。time はdatetime 型データに許容されるデータ形式のどれかで指定するか、あるいはローカル変数として指定することができます。日付は指定できません。ですから、datetime 値のデータ部分は許容されません。
TIME:
SQL Server に指定の時刻まで待つように指示します。
例:
BEGIN
WAITFOR TIME '22:20'
EXECUTE update_all_stats
END
ストアド プロシージャ中のパラメータの指定
ストアド プロシージャ中でパラメータを指定するには、次の構文を使用します。
| Oracle | Microsoft SQL Server |
| Varname datatype DEFAULT <value>; |
{@parameter data_type} [VARYING] [= default] [OUTPUT] |
トリガ
Oracle にも Microsoft SQL Server にもトリガはありますが、その実装は多少違っています。
| 説明 | Oracle | Microsoft SQL Server |
| 1テーブル当たりのトリガ数 | 制限なし | 制限なし |
| INSERT、UPDATE、DELETE の前に実行されるトリガ | ある | ない |
| INSERT、UPDATE、DELETE の後に実行されるトリガ | ある | ある |
| ステートメント レベル トリガ | ある | ある |
| 行レベル トリガ | ある | ない |
| 実行前にチェックされる制約 | ある。トリガが使用不可になっているときを除く。 | ある。また、これはデータ変換サービスのオプションでもある。 |
| UPDATE または DELETE トリガ中の旧値または前の値の参照 | :old | DELETED.column |
| INSERT トリガ中の新しい値の参照 | :new | INSERTED.column |
| トリガを使用不可に設定 | ALTER TRIGGER | データ変換サービスのオプション |
DELETED と INSERTED は、トリガ ステートメントのために SQL Server によって作成される論理 (概念) テーブルです。その構造はトリガが定義され、ユーザーの操作によって変更される可能性のある行の旧値または新値が保存されるテーブルと似ています。このテーブルは Transact-SQL 中の行レベルの変更を追跡します。これらのテーブルは Oracle の行レベルのトリガと同じ機能をもっています。SQL Server では INSERT、UPDATE、DELETE の各ステートメントが実行されると、行がトリガ テーブルと INSERTED および DELETED テーブルに同時に追加されます。
INSERTED および DELETED テーブルは、トリガ テーブルとまったく同じです。その列名は同じで、データ型も同じです。たとえば、GRADE テーブルにトリガが設定されている場合、INSERTED および DELETED テーブルの構造は次のようになります。
| GRADE | INSERTED | DELETED |
| SSN CHAR(9) CCODE VARCHAR(4) GRADE VARCHAR(2) |
SSN CHAR(9) CCODE VARCHAR(4) GRADE VARCHAR(2) |
SSN CHAR(9) CCODE VARCHAR(4) GRADE VARCHAR(2) |
INSERTED および DELETED テーブルはどのような種類のトリガ アクションを実行すべきかを決定するためにトリガによって調べられます。INSERTED テーブルは INSERT および UPDATE ステートメントで使用されます。DELETED テーブルは DELETE および UPDATE ステートメントで使用されます。
UPDATE ステートメントは INSERTED および DELETED テーブルを両方とも使用しますが、それは SQL Server では UPDATE 操作が実行されるときはいつでも古い行が削除され、新しい行が挿入されるからです。その結果、UPDATE が実行されるときには、INSERTED テーブル中の行がつねに DELETED テーブル中にコピーされます。
次の例は PL/SQL 行レベル トリガを INSERTED および DELETED テーブルに置き換えた例です。いずれのテーブルでもその全行のクエリには完全な外部結合が使用されます。
| Oracle | Microsoft SQL Server |
| CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES AFTER INSERT OR UPDATE OR DELETE ON STUDENT_ADMIN.GRADE FOR EACH ROW BEGIN INSERT INTO GRADE_HISTORY( TABLE_USER, ACTION_DATE, OLD_SSN, OLD_CCODE, OLD_GRADE, NEW_SSN, NEW_CCODE, NEW_GRADE) VALUES (USER, SYSDATE, :OLD.SSN, :OLD.CCODE, :OLD.GRADE, :NEW.SSN, :NEW.CCODE, :NEW.GRADE), END; |
CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES ON STUDENT_ADMIN.GRADE FOR INSERT, UPDATE, DELETE AS INSERT INTO GRADE_HISTORY( TABLE_USER, ACTION_DATE, OLD_SSN, OLD_CCODE, OLD_GRADE NEW_SSN, NEW_CCODE, NEW_GRADE) SELECT USER, GETDATE(), OLD.SSN, OLD.CCODE, OLD.GRADE, NEW.SSN, NEW.CCODE, NEW.GRADE FROM INSERTED NEW FULL OUTER JOIN DELETED OLD ON NEW.SSN = OLD.SSN |
トリガを作成できるのはカレント データベース中だけですが、カレント データベース外のオブジェクトも参照することができます。トリガを区別するためにに所有者名を使用した場合は、テーブル名も同じ方法で区別することです。
トリガは最高32レベルの深さまでネストすることができます。トリガであるテーブルを変更し、そのテーブルに別のトリガが設定されている場合は、それが 2 つめのトリガを起動し、さらにそれが3つめのトリガを起動する、というふうになります。この連鎖中のトリガのどれかによって無限ループが発生する場合は、ネスト レベルを超えてしまい、そのトリガが取り消されます。また、テーブルのある列に対する更新トリガによって別の列の更新が発生する場合、その更新トリガは一回だけ起動されます。
Microsoft SQL Server の宣言参照整合性 (DRI) は、データベースにまたがる参照整合性を提供しません。データベースにまたがる参照整合性が必要なときは、トリガを使用することです。次のステートメントは Transact-SQL トリガ中で許容されないものです。
CREATE ステートメント (DATABASE、TABLE、INDEX、PROCEDURE、DEFAULT、RULE、TRIGGER、SCHEMA、VIEW)
DROP ステートメント(TRIGGER、INDEX、TABLE、PROCEDURE、DATABASE、VIEW、DEFAULT、RULE)
ALTER ステートメント(DATABASE、TABLE、VIEW、PROCEDURE、TRIGGER)
TRUNCATE TABLE
GRANT, REVOKE, DENY
UPDATE STATISTICS
RECONFIGURE
UPDATE STATISTICS
RESTORE DATABASE, RESTORE LOG
LOAD LOG, DATABASE
DISK ステートメント
SELECT INTO (テーブルを作成するため)
トリガについての詳細は、SQL Server Books Online を参照してください。