トリガーは特殊な種類のストアドプロシージャで、通常のストアドプロシージャとは異なります。トリガーはイベントによって自動的に呼び出され実行されますが、ストアドプロシージャは名前で呼び出されます。
Ø トリガーとは
トリガーはテーブルに対する挿入、更新、削除操作時に自動的に実行される特殊なストアドプロシージャです。トリガーは通常、CHECK制約よりも複雑な制約チェックに使用されます。トリガーと通常のストアドプロシージャの違いは、特定のテーブルに対して操作（update、insert、deleteなど）が行われた際に、システムが自動的に対応するトリガーを実行することです。SQL Server 2005ではトリガーは2種類に分類されます：DMLトリガーとDDLトリガー。DDLトリガーはcreate、alter、dropなどのデータ定義言語ステートメントによって起動されます。
DMLトリガーは以下のように分類されます：
- afterトリガー（後実行トリガー）
a. insertトリガー
b. updateトリガー
c. deleteトリガー
- instead ofトリガー（代わり実行トリガー）
afterトリガーはinsert、update、delete操作の後にのみ実行され、テーブルでのみ定義できます。instead ofトリガーは定義された操作（insert、update、delete）を実行せず、トリガー本体のみを実行します。テーブルだけでなくビューにも定義できます。
トリガーには2つの特殊なテーブルがあります：挿入テーブル（insertedテーブル）と削除テーブル（deletedテーブル）。これらは論理テーブルであり、仮想テーブルです。システムがメモリ上に作成しますが、データベースには保存されません。これらのテーブルは読み取り専用で、データの読み取りのみ可能で変更はできません。これらのテーブルの構造は、トリガーが適用されるテーブルの構造と常に同じです。トリガーの処理が完了すると、これらのテーブルは削除されます。insertedテーブルには挿入または更新後のデータが格納され、deletedテーブルには更新前または削除されたデータが格納されます。
| テーブル操作 | Inserted論理テーブル | Deleted論理テーブル |
|---|---|---|
| レコード追加（insert） | 追加されたレコード | なし |
| レコード削除（delete） | なし | 削除されたレコード |
| レコード更新（update） | 更新後のレコード | 更新前のレコード |
更新操作では、まずレコードを削除してから新しいレコードを挿入します。そのため、insertedとdeletedテーブルの両方に更新後のデータレコードが存在します。重要な点は：トリガー自体がトランザクションであるため、トリガー内でデータの変更に対して特殊なチェックを実行できます。条件を満たさない場合は、トランザクションをロールバックして操作を取り消すことができます。
Ø トリガーの作成
構文
create trigger トリガー名<br></br>on テーブル名<br></br>with encryption --トリガーの暗号化<br></br> for update...<br></br>as<br></br> Transact-SQL
INSERTトリガーの作成
--INSERTトリガーの作成<br></br>if (object_id('ins_classes_trigger', 'tr') is not null)<br></br> drop trigger ins_classes_trigger<br></br>go<br></br>create trigger ins_classes_trigger<br></br>on classes<br></br> for insert --挿入トリガー<br></br>as<br></br> --変数の定義<br></br> declare @class_id int, @class_name varchar(20), @temp_id int;<br></br> --insertedテーブルから挿入されたレコード情報を取得<br></br> select @class_id = id, @class_name = name from inserted;<br></br> set @class_name = @class_name + convert(varchar, @class_id);<br></br> set @temp_id = @class_id / 2; <br></br> insert into students values(@class_name, 18 + @class_id, @temp_id, @class_id);<br></br> print '学生が正常に追加されました！';<br></br>go<br></br>--データの挿入<br></br>insert into classes values('6組', getDate());<br></br>--データの確認<br></br>select * from classes;<br></br>select * from students order by id;
INSERTトリガーは、insertedテーブルに新しく挿入されたレコードを追加します。
DELETEトリガーの作成
--DELETEトリガーの作成<br></br>if (object_id('del_classes_trigger', 'TR') is not null)<br></br> drop trigger del_classes_trigger<br></br>go<br></br>create trigger del_classes_trigger<br></br>on classes<br></br> for delete --削除トリガー<br></br>as<br></br> print 'データをバックアップ中……'; <br></br> if (object_id('classes_backup', 'U') is not null)<br></br> --classes_backupが存在する場合は直接データを挿入<br></br> insert into classes_backup select name, createDate from deleted;<br></br> else<br></br> --classes_backupが存在しない場合は作成してから挿入<br></br> select * into classes_backup from deleted;<br></br> print 'データバックアップが成功しました！';<br></br>go<br></br>--<br></br>--影響を受けた行数を表示しない<br></br>--set nocount on;<br></br>delete classes where name = '6組';<br></br>--データの確認<br></br>select * from classes;<br></br>select * from classes_backup;
DELETEトリガーは、データを削除する際に、削除されたデータをdeletedテーブルに保存します。
UPDATEトリガーの作成
--UPDATEトリガーの作成<br></br>if (object_id('upd_classes_trigger', 'TR') is not null)<br></br> drop trigger upd_classes_trigger<br></br>go<br></br>create trigger upd_classes_trigger<br></br>on classes<br></br> for update<br></br>as<br></br> declare @old_class_name varchar(20), @new_class_name varchar(20);<br></br> --更新前のデータ<br></br> select @old_class_name = name from deleted;<br></br> if (exists (select * from students where name like '%'+ @old_class_name + '%'))<br></br> begin<br></br> --更新後のデータ<br></br> select @new_class_name = name from inserted;<br></br> update students set name = replace(name, @old_class_name, @new_class_name) where name like '%'+ @old_class_name + '%';<br></br> print '関連データの更新が成功しました！';<br></br> end<br></br> else<br></br> print 'studentsテーブルを更新する必要はありません！';<br></br>go<br></br>--データの確認<br></br>select * from students order by id;<br></br>select * from classes;<br></br>update classes set name = '六組' where name = '6組';
UPDATEトリガーは、データを更新した後、更新前のデータをdeletedテーブルに、更新後のデータをinsertedテーブルに保存します。
列レベルのUPDATEトリガー
if (object_id('upd_class_column_trigger', 'TR') is not null)<br></br> drop trigger upd_class_column_trigger<br></br>go<br></br>create trigger upd_class_column_trigger<br></br>on classes<br></br> for update<br></br>as<br></br> --列レベルトリガー：クラス作成日が更新されたかどうかを確認<br></br> if (update(createDate))<br></br> begin<br></br> raisError('システム通知：クラス作成日時は変更できません！', 16, 11);<br></br> rollback tran;<br></br> end<br></br>go<br></br>--テスト<br></br>select * from students order by id;<br></br>select * from classes;<br></br>update classes set createDate = getDate() where id = 3;<br></br>update classes set name = '四組' where id = 7;
列レベルのトリガーでは、update()関数を使用して特定の列が更新されたかどうかを判断できます。
INSTEAD OFトリガー
INSTEAD OFトリガーは、定義された操作（insert、update、delete）を実行せず、トリガー本体のみを実行します。
作成構文
create trigger トリガー名<br></br>on テーブル名<br></br>with encryption<br></br> instead of update...<br></br>as<br></br> T-SQL
INSTEAD OFトリガーの作成
if (object_id('instead_of_trigger', 'TR') is not null)<br></br> drop trigger instead_of_trigger<br></br>go<br></br>create trigger instead_of_trigger<br></br>on classes<br></br> instead of delete/*, update, insert*/<br></br>as<br></br> declare @class_id int, @class_name varchar(20);<br></br> --削除される情報をクエリし、変数に代入<br></br> select @class_id = id, @class_name = name from deleted;<br></br> print 'ID: ' + convert(varchar, @class_id) + ', 名前: ' + @class_name;<br></br> --まずstudentsの情報を削除<br></br> delete students where cid = @class_id;<br></br> --次にclassesの情報を削除<br></br> delete classes where id = @class_id;<br></br> print 'ID: ' + convert(varchar, @class_id) + ', 名前: ' + @class_name + ' の情報が正常に削除されました！';<br></br>go<br></br>--テスト<br></br>select * from students order by id;<br></br>select * from classes;<br></br>delete classes where id = 7;
カスタムメッセージの表示 raiserror
if (object_id('msg_trigger', 'TR') is not null)<br></br> drop trigger msg_trigger<br></br>go<br></br>create trigger msg_trigger<br></br>on students<br></br> after insert, update<br></br>as raisError('msg_triggerがトリガーされました', 16, 10);<br></br>go<br></br>--テスト<br></br>insert into students values('lily', 22, 1, 7);<br></br>update students set sex = 0 where name = 'lucy';<br></br>select * from students order by id;
トリガーの変更
alter trigger msg_trigger<br></br>on students<br></br>after delete<br></br>as raisError('msg_triggerがトリガーされました', 16, 10);<br></br>go<br></br>--テスト<br></br>delete from students where name = 'lucy';
トリガーの有効化と無効化
--トリガーの無効化<br></br>disable trigger msg_trigger on students;<br></br>--トリガーの有効化<br></br>enable trigger msg_trigger on students;
作成されたトリガー情報のクエリ
--既存のトリガーのクエリ<br></br>select * from sys.triggers;<br></br>select * from sys.objects where type = 'TR';<br></br><br></br>--トリガーのトリガーイベントの確認<br></br>select te.* from sys.trigger_events te join sys.triggers t<br></br>on t.object_id = te.object_id<br></br>where t.parent_class = 0 and t.name = 'valid_data_trigger';<br></br><br></br>--トリガー作成ステートメントの確認<br></br>exec sp_helptext 'msg_trigger';
例：挿入データの検証
if ((object_id('valid_data_trigger', 'TR') is not null))<br></br> drop trigger valid_data_trigger<br></br>go<br></br>create trigger valid_data_trigger<br></br>on students<br></br>after insert<br></br>as<br></br> declare @age int,<br></br> @name varchar(20);<br></br> select @name = s.name, @age = s.age from inserted s;<br></br> if (@age < 18)<br></br> begin<br></br> raisError('挿入されたデータの年齢に問題があります', 16, 1);<br></br> rollback tran;<br></br> end<br></br>go<br></br>--テスト<br></br>insert into students values('forest', 2, 0, 7);<br></br>insert into students values('forest', 22, 0, 7);<br></br>select * from students order by id;
例：操作ログ
if (object_id('operation_log', 'U') is not null)<br></br> drop table operation_log<br></br>go<br></br>create table operation_log(<br></br> id int identity(1, 1) primary key,<br></br> action varchar(20),<br></br> createDate datetime default getDate()<br></br>)<br></br>go<br></br>if (exists (select * from sys.objects where name = 'student_log_trigger'))<br></br> drop trigger student_log_trigger<br></br>go<br></br>create trigger student_log_trigger<br></br>on students<br></br>after insert, update, delete<br></br>as<br></br> if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))<br></br> begin<br></br> insert into operation_log(action) values('updated');<br></br> end<br></br> else if (exists (select 1 from inserted) and not exists (select 1 from deleted))<br></br> begin<br></br> insert into operation_log(action) values('inserted');<br></br> end<br></br> else if (not exists (select 1 from inserted) and exists (select 1 from deleted))<br></br> begin<br></br> insert into operation_log(action) values('deleted');<br></br> end<br></br>go<br></br>--テスト<br></br>insert into students values('king', 22, 1, 7);<br></br>update students set sex = 0 where name = 'king';<br></br>delete students where name = 'king';<br></br>select * from operation_log;<br></br>select * from students order by id;