SQL Server: トリガーの詳細な説明

1. 概要

トリガーは、明示的に呼び出すことはできない特殊なストアド プロシージャですが、テーブルにレコードが挿入、更新、または削除されると自動的にアクティブになります。 したがって、トリガーを使用して、テーブルに複雑な整合性制約を実装できます。

2. トリガーの分類

SQL Server2000 には、「Instead of」トリガーと「After」トリガーの 2 種類のトリガーが用意されています。

テーブルまたはビューの各変更アクション (挿入、更新、および削除) には「代わりに」トリガーを設定でき、テーブルの各変更アクションには複数の「後」トリガーを設定できます。

2.1 「Instead of」トリガー

「Instead of」トリガーは、実際の「挿入」が実行される前に実行されます。テーブルに加えて、ビューで「代わりに」トリガーを使用して、ビューがサポートできる更新操作を拡張することもできます。

「代わりに」トリガーは、実行される SQL ステートメントを置き換えます。つまり、実行される SQL ステートメントは「実際には実行されません」

alter trigger trigger_学生_Delete
on 学生
instead of Delete
as
begin
    select 学号, 姓名 from deleted
end
 
delete from 学生 where 学号 = 4

上の例では、「トリガー Student_Delete」トリガーが定義されています。プロセッサは、「削除」テーブルから削除する生徒を出力します。「削除」操作を実行した後、「生徒番号 = 4」の生徒が削除されていないことがわかります。これは、「生徒の削除をトリガーしたため」です。 「」は、実行される「trigger students Delete」を置き換えます。delete from students where students number = 4」ステートメントですが、「trigger students_Delete」では実際に生徒は削除されません。

2.2 「後」トリガー

「後」トリガーは、Insert、Update、または Deleted ステートメントの実行後にトリガーされます。 「After」トリガーはテーブルでのみ使用できます。

「After」トリガーは主に、テーブルの変更後 (挿入、更新、または削除操作の後) に他のテーブルを変更するために使用されます

3. 挿入テーブルと削除テーブル

SQL Server は、特殊テーブルごとに 2 つのトリガーを作成します: 挿入テーブルおよび削除されたテーブル。

これら 2 つのテーブルはシステムによって維持され、データベースではなくメモリ内に存在し、仮想テーブルとして理解できます。

これら 2 つのテーブルの構造は、トリガーによって作用されるテーブルの構造と常に同じです。

トリガーの実行完了後、トリガーに関連する2つのテーブルも削除されます。

削除済みテーブルには、Delete ステートメントまたは Update ステートメントの実行によりテーブルから削除されるすべての行が格納されます。

Inserted テーブルには、Insert または Update ステートメントの実行によりテーブルに挿入されるすべての行が格納されます。

4. トリガー実行プロセス

Insert、update、または delete ステートメントが制約に違反する場合、この SQL ステートメントは正常に実行されないため、「After」トリガーはアクティブになりません。

「代わりに」トリガーは、それを起動したアクションの代わりに実行できます。これは、挿入されたテーブルと削除されたテーブルが作成されたばかりで、他の操作が発生していないときに実行されます。 「Instead of」トリガーは制約の前に実行されるため、制約の前処理を行うことができます。

5. トリガーを作成します

create trigger trigger_name
on  {table_name|view_name}
{After|Instead of} {insert|update|delete}
as 相应T-SQL语句

6. トリガーを変更します:

alter trigger trigger_name
on  {table_name|view_name}
{After|Instead of} {insert|update|delete}
as 相应T-SQL语句

8. トリガーを削除します。トリガーイン:

8.1 データベース内のすべてのトリガーを表示します

drop trigger trigger_name

8.2 単一のトリガーを表示します

select * from sysobjects where xtype='TR'

9. 「代わりに」関連する例:

2 つのテーブル:student (student Number int, name varchar)、貸出記録 (学生番号 int、図書番号 int)

実装された関数: 学生テーブルを削除するとき、学生に貸出記録 (未返却) が残っている場合は削除できません

exec sp_helptext '触发器名'

「10. 「トリガー

10.1 「受注」テーブルにトリガーを作成します。「受注」テーブルに受注レコードを挿入する際、「商品」テーブルの商品ステータスの「ステータス」が1(整理中)かどうかを確認します。注文を「注文」テーブルに追加することはできません。

alter trigger trigger_学生_Delete
on 学生
instead of Delete
as
begin
    if not exists(select * from 借书记录, deleted where 借书记录.学号 = deleted.学号)
        delete from 学生 where 学生.学号 in (select 学号 from deleted)
end

この例では、「pid」が商品コードです

​​

この例のif判定は厳密に言えば不正確です、なぜなら毎回「order」テーブルにレコードを挿入していれば問題ないからです複数のレコードが挿入された場合、「選択ステータス」は複数の行を返します。

10.2 「注文」テーブルに挿入トリガーを作成し、注文を追加する際に、「商品」テーブルの対応する商品レコードの在庫を削減します。

りー

10.3 在“商品”表建立删除触发器，实现“商品”表和“订单”表的级联删除。

create trigger goodsdelete trigger_商品_delete
on 商品
after delete
as
    delete from 订单 where 订单.pid in (select pid from deleted)

   

10.4 在“订单”表建立一个更新触发器，监视“订单”表的“订单日期”列，使其不能被“update”.

create trigger trigger_订单_update
on 订单
after update
as
    if update(订单日期)
    begin
        raiserror('订单日期不能手动修改',10,1)
        rollback transaction
    end

   

10.5 在“订单”表建立一个插入触发器，保证向“订单”表插入的货品必须要在“商品”表中一定存在。

create trigger trigger_订单_insert3
on 订单
after insert
as
    if (select count(*) from 商品, inserted where 商品.pid = inserted.pid)=0
    begin
        print '商品不存在'
        rollback transaction
    end

   

10.6 “订单”表建立一个插入触发器，保证向“订单”表插入的货品信息要在“订单日志”表中添加

alter trigger trigger_订单_insert
on 订单
for insert
as
    insert into 订单日志 select inserted.Id, inserted.pid,inserted.数量 from inserted