SQLトリガーの例の説明

定義: トリガーとは何ですか? SQL Serverでは、特定のテーブルに対して特定の操作を実行し、特定の条件をトリガーして実行されるプログラムのことです。トリガーは特別なストアド プロシージャです。
3 つの一般的なトリガーがあります: それぞれ挿入、更新、および削除イベントに適用されます。

なぜトリガーを使用する必要があるのですか?たとえば、次の 2 つのテーブル:

      Create Table Student(              --学生表 
        StudentID int primary key,       --学号 
        .... 
       )

      Create Table BorrowRecord(               --学生借书记录表 
        BorrowRecord   int identity(1,1),       --流水号   
        StudentID      int ,                    --学号 
        BorrowDate     datetime,                --借出时间 
        ReturnDAte     Datetime,                --归还时间 
        ... 
      )

使用する関数は次のとおりです:
1. 学生の学生IDを変更した場合、その学生の貸出記録がその学生に関連付けられたままであることを願っています(つまり、貸出記録テーブルの学生IDも同時に変更されます)。 );
2. 学生が卒業した場合、学生証を削除し、図書の貸出記録も削除したいと考えています。
待って。

現時点ではトリガーを使用できます。 1 の場合、Update トリガーを作成します:

Create Trigger truStudent
On Student – 学生テーブルにトリガーを作成します
for Update​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​
BEGIN p 更新BORROWRECORD
Studentid = I.Studentid を設定します
BORROWRECORD から Br、削除 D、挿入 I-削除および挿入された一時テーブル
ENTID d END

トリガーを理解する 2 つの一時テーブル: 削除と挿入。 Deleted と Inserted はそれぞれ、イベントをトリガーしたテーブルの「古いレコード」と「新しいレコード」を表すことに注意してください。
データベース システムには、テーブルに記録された情報を保存するために使用される 2 つの仮想テーブルがあります。レコードは保存されません
更新に使用される新しいレコードを保存します
: 新しいレコードを生成します。 Inserted テーブルに古いレコードをコピーし、Student レコードを削除して新しいレコードを書き込みます。

2 の場合、削除トリガーを作成します
トリガー trdStudent を作成します
生徒に
削除用
As
From BorrowRecord br , Delted d
Where br.StudentID=d.StudentID

これら 2 つの例から、トリガーの鍵がわかります。A. 2 つの一時テーブル B. トリガー メカニズム。

SQLトリガーインスタンス2

/*
テーブル [タバコ在庫テーブル]、テーブル [タバコ販売テーブル] を含む仮想テスト環境を確立します。
これら 2 つのテーブルのデータの追跡に注意し、トリガーがどのようなビジネス ロジックを実行し、それがデータにどのような影響を与えるかを理解してください。
トリガーの役割をより明確に表現するために、テーブル構造はデータの冗長性を持ち、第 3 正規形に準拠していないことを説明します。
*/
USE Master
GO

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = 'Cigarette Inventory Table')
DROP TABLE Cigarette Inventory Table
GO
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = 'タバコ販売テーブル')
DROP TABLE タバコ販売テーブル
GO

-- ビジネス ルール: 販売額 = 販売数量 * 販売単価 ビジネス ルール。

CREATE TABLE タバコ販売テーブル
(
タバコブランド VARCHAR(40) PRIMARY KEY NOT NULL,
購入者 VARCHAR(40) NULL,
販売数量 INT NULL,
販売単価 MONEY NULL,
売上金額 MONEY NULL
)
GO

-- ビジネス ルール: 在庫量 = 在庫数量 * 在庫単価 ビジネス ルール。

CREATE TABLE タバコ在庫テーブル
(
タバコブランド VARCHAR(40) PRIMARY KEY NOT NULL,
在庫数量 INT NULL,
在庫単価 MONEY NULL,
在庫金額 MONEY NULL
)
GO

--トリガーの作成、例 1

/*
トリガー [T_INSERT_cigarette inventory table] を作成します。このトリガーは比較的単純です。
説明: [Cigarette Inventory Table] で INSERT アクションが発生するたびに、このトリガーがトリガーされます。
トリガー機能: ビジネスルールを適用して、挿入されたデータ内で在庫量 = 在庫数量 * 在庫単価となるようにします。
注: [INSERTED] と [DELETED] はシステム テーブルであり、作成、変更、削除はできませんが、呼び出すことはできます。
重要: これら 2 つのシステム テーブルの構造は、データが挿入されるテーブルの構造と同じです。
*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_Cigarette Inventory Table')
DROP TRIGGER T_INSERT_Cigarette Inventory Table
GO

CREATE TRIGGER T_INSERT_Cigarette Inventory Table
タバコの在庫表
挿入用
AS
--トランザクション処理を送信します
BEGIN TRANSACTION
--ビジネス ルールを確実にするために次のステートメントの実行を強制します
UPDATE タバコ在庫テーブル
SET 在庫量 = 在庫数量 * 在庫単価
WHERE タバコ ブランド IN (SELECT Cigarettes Brand from INSERTED)
COMMIT TRANSACTION
GO

/*
[タバコ在庫テーブル] のテスト データを挿入:
最初のデータ (紅塔山新勢力) のデータはビジネス ルールに準拠していることに注意してください。
2 番目のデータ (紅塔では山 (人間峰) では、[在庫量] が空であり、ビジネス ルールに準拠していません
3 番目のデータ (雲南省の画像) では、[在庫量] に [在庫量] を乗算した値が一致しません。業務ルールに適合しない【在庫単価】です。
4番目のデータ在庫数量は0です。
なお、データ挿入後、【タバコ在庫表】のデータが、在庫量＝在庫数量×在庫単価となっているかをご確認ください。
*/

INSERT INTO タバコ在庫表（タバコブランド、在庫数量、在庫単価、在庫量）
SELECT '紅塔山新勢力',100,12,1200 UNION ALL
SELECT '紅塔山人工峰',100,22 、NULL UNION ALL
SELECT '雲南画像',100,60,500 UNION ALL
SELECT 'Yuxi',0,30,0
GO

--クエリデータ

SELECT * FROM タバコ在庫テーブル
GO
/*

results Set

RecordId タバコブランド 在庫数 在庫単価 在庫量
-------- ----- -------- --- ---- -- -------
1 紅塔山ニューフォース 100 12.0000 1200.0000
2 紅塔山人工峰 100 22.0000 2200.0000
3 雲南画像 100 60.0000 6000.0000
4 玉渓 0 30.000 0 .0000

(影響を受ける行数は 4 行です)

*/

-- トリガーの例 2

/*
より複雑なトリガー [T_INSERT_Cigarette Sales Table] を作成します。
説明: [Cigarette Inventory Table] で INSERT アクションが発生するたびに、このトリガーが起動されます。
トリガー機能: ビジネスルールを実装します。
ビジネスルール: 販売されたタバコのブランドの在庫が存在しないか、在庫がゼロの場合、エラーが返されます。
そうしないと、【タバコ在庫表】の該当銘柄のタバコの在庫数と数量が自動的に減ります。
*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_Cigarette Sales Table')
DROP TRIGGER T_INSERT_Cigarette Sales テーブル
GO

CREATE TRIGGER T_INSERT_Cigarette Sales Table
ON tobacco Sales テーブル
挿入用
AS
トランザクションを開始
-- データの合法性を確認します: 販売されたタバコの在庫があるかどうか、または在庫がゼロより大きいかどうか
存在しない場合 (
在庫数量を選択
タバコ在庫テーブルから
どこにタバコのブランドが入っているか ( SELECT タバコのブランド FROM INSERTED)
)
BEGIN
--エラー メッセージを返す
RAISERROR('エラー! タバコの在庫が存在しないため販売できません。',16,1)
-- トランザクションのロールバック
ROLLBACK
RETURN
END SIF が存在します タバコの在庫が 0 以下であるため販売できません ',16,1)
--ロールバック トランザクション
ROLLBACK
RETURN
END

--法的データを処理します

-- の実行を強制します。ビジネスルールを確実にするために次のステートメント
UPDATE タバコ販売表
SET 販売額 = 販売数量 * 販売単価
WHERE タバコブランド IN (挿入されたタバコブランドから選択)

DECLARE @Cigarette Brand VARCHAR(40)
SET @Cigarette Brand = (INSERTED からタバコのブランドを選択)

DECLARE @販売数量 MONEY
SET @販売数量 = (INSERTED から販売数量を選択)

UPDATE タバコ在庫表
SET 在庫数量 = 在庫数量 - @販売数量,
在庫金額 = (在庫数量 - @販売数量) ※在庫単価
WHERE タバコブランド = @cigarette brand
COMMIT TRANSACTION
GO

--[タバコ在庫表]と[タバコ販売表]のデータ変更はご自身で追跡してください。
--[Cigarette Sales Table] の場合、最初のテスト データを挿入します。これは正常です。

タバコ販売テーブルに挿入 (タバコのブランド、購入者、販売数量、販売単価、販売額)
SELECT 'Hongtashan New Force'、'購入者'、10、12、1200
GO

-- [タバコ] の場合[売上テーブル] に 2 番目のテスト データを挿入します。データ「売上金額」が「売上単価 * 売上数量」に等しくありません。
--トリガーは、販売金額が販売単価 * 販売数量と等しくなるようにデータを自動的に修正します。

タバコ販売テーブルに挿入 (タバコのブランド、購入者、販売数量、販売単価、販売額)
SELECT 'Hongtashan Renrenfeng','購入者',10,22,2000
GO

-- [タバコ販売] の場合テーブル] に 3 番目のテスト データを挿入します。このデータのタバコの銘柄はタバコ在庫テーブルには見つかりません。
--トリガーはエラーを報告します。

INSERT INTO タバコ販売テーブル (タバコのブランド、購入者、販売数量、販売単価、販売額)
SELECT 'Honghe V8','a Purchaser',10,60,600
GO

/*
結果セット
サーバー:メッセージ 50000、レベル 16、状態 1、プロセス T_INSERT_Cigarette Sales テーブル、行 15
エラー!タバコは在庫がないので販売できません。
*/

-- [タバコ販売テーブル] の場合、3 番目のテスト データを挿入します。このデータのタバコ ブランドのタバコ在庫テーブルの在庫は 0 です。
-- トリガーはエラーを報告します。

INSERT INTO タバコ販売テーブル (タバコのブランド、購入者、販売数量、販売単価、販売額)
SELECT 'Yuxi','購入者',10,30,300
GO

/*
結果セット
サーバー: メッセージ50000、レベル 16、状態 1、プロセス T_INSERT_Cigarette Sales テーブル、行 29
エラー!タバコの在庫が 0 以下のため販売できません。
*/
--クエリデータ
SELECT * FROM タバコ在庫テーブル

SELECT * FROM Cigarette Sales Table
GO

/*
補足:
1. この例では、主に単純なビジネス ルールの実装を通じてトリガーの使用について説明します。トリガー INSERTED と DELETED の 2 つのシステム テーブルを理解して使用する必要があります。この例で作成されたトリガーはすべて FOR INSERT です。具体的な構文については、
////////// を参照してください。 /////// ////////////////////////////////////////// ////////// ////////////////////////////////////// ///////////// /////////////

////////////////////////////////////////////// /// ////////////////////////////////////////////// /////// ///////////////////////////


CREATE TRIGGER トリガー名
ON { テーブル ビュー }
[ WITH ENCRYPTION ] -- 暗号化トリガーに使用されます
{
{ { FOR | AFTER OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( 列 )
[ { AND | OR } UPDATE ( 列 ) ]
[ ...n ]
IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ 比較演算子 } column_bitmask [ ...n ]
} ]
sql_state ment [...n]
}
}

4. トリガーに関しては、
(1)、DELETE トリガーは TRUNCATE TABLE ステートメントをキャプチャできないことにも注意してください。
(2)、次の Transact-SQL ステートメントはトリガーでは許可されません:
ALTER DATABASE CREATE DATABASE DISK INIT
DISK RESIZE DROP DATABASE LOAD DATABASE
LOAD LOG RECONFIGURE RESTORE DATABASE
RESTORE LOG
(3)、トリガーほとんどのフロアは32階です。

*/

--トリガーを変更する
--本質的には、CREATE TRIGGER... を ALTER TRIGGER... に変更するだけです。

--トリガーの削除
DROP TRIGGER xxx
GO

--テスト環境の削除
DROP TABLE タバコ在庫テーブル
GO
DROP TABLE タバコ販売テーブル
GO
DROP TRIGGER T_INSERT_Cigarette在庫テーブル
GO
DROP TRIGGER T_INSERT _タバコ販売リスト
行く
################################################# ################
トリガーの基本的な知識と例
: テーブル/ビューにトリガー tr_name を作成します
{for | after | } [update][,][insert ][,][削除]
[暗号化あり]
as {batch | if update (col_name) [{and|or} update (col_name)] }

説明:
1 tr_name: テーブル/ビューのトリガー名
2 : トリガーが作用するテーブル。トリガーは 1 つのテーブルにのみ作用できます
3 以降: 同義語
4 以降と代わり: SQL 2000 の新しい項目の前後との違い
After
はトリガー イベントの発生後にアクティブ化され、作成できるのは On のみですテーブル
の代わりに
が、テーブルまたはビュー
5 で確立できる、対応するトリガー イベントの代わりに実行されます。 挿入、更新、削除: トリガーをアクティブにする 3 つの操作を同時に実行できます。またはオプション 1 つ
6 if update (col_name): 操作が指定された列に影響を与えるかどうかを示します。影響がある場合は、トリガーをアクティブにします。また、削除操作は行のみに影響するので
、削除操作を使用する場合にはこのステートメントは使用できません(使用してもエラーにはなりませんが、トリガーが起動できなくなり意味がありません)。
7 トリガーの実行時に使用される 2 つの特別なテーブル: delete と insert
deleted とinsert は、トリガーのアクティブ化時にシステムによって自動的に生成される特別な一時テーブルであると言えます。その構造とトリガー機能テーブルの構造は次のとおりです。同じですが、保存されるデータが異なります。

続き
次の表は、削除されたデータと挿入されたデータの違いを説明しています
削除されたデータと挿入されたデータの違い
挿入
挿入および更新操作後にデータを保存します
削除済み
削除および更新操作の前にデータを保存します
注: 更新操作最初に削除が実行され、次に挿入が実行されるため、更新操作を実行すると、変更前のデータのコピーが削除されたテーブルにコピーされ、変更されたデータはトリガーが適用されるテーブルに保存されます。挿入されたテーブルにもコピーが生成されます。中

/////////

トリガーの作成[TRIGGER admixture_receive_log] オン dbo.chl_lydj
更新用
AS
begin
declare @djsfxg char(10) declare @wtbh char(20)
select @wtbh=wtbh from inserted
update ly_tzk set djsfxg='已修改' where wtbh=@wtbh
end
if (select data_sfjl from t_logsetup)='是’
begin
declare @oldcjmc char (100) declare @oldlyrq datetime
declare @oldbzbh char (60)宣言 @oldzl char (20)
destroy @olddj char (10)

declare @newcjmc char (100) declare @newlyrq datetime
declare @newbzbh char (60) declare @newzl char (20)
declare @newdj char (10)

declare @xgr char (20)

select @oldcjmc=cjmc,@oldlyrq=lyrq,@oldbzbh=bzbh,@oldzl=zl,@olddj=dj from deleted
select @newcjmc= cjmc,@newlyrq=lyrq,@ newbzbh=bzbh,@newzl=zl,@newdj=dj from inserted
select @xgr=xgr from t_modifyuser where @wtbh=wtbh

if @oldcjmc@new cjmc
begin
t_modifylogに挿入(wtbh, mod_time, mod_table) , mod_field, ori_value, now_value, mod_people) values
(@wtbh,getdate(), 'chl_lydj','cjmc', @oldcjmc, @newcjmc, @xgr)
end

終了
///// /////修正時、直接操作して'create'を'alter'に変更できます

/////////////////////////

CREATE TRIGGER [TRIGGER ly_tzk_syf] ON dbo.ly_tzk
FOR insert
AS
begin
declare @clmc char(100) declare @dwbh char(100) declare @syf char(100) declare @dwgcbh char(100) declare @wtbh char(50)
declare @dj_1 money declare @feiyong_z money declare @feiyong_xf money declare @feiyong_sy money
declare @dj char(20)
選択 @wtbh=wtbh、 @clmc=clmc、 @dwbh=dwbh、@syf =syf from inserted
select @dj=dj from feihao_bz where clmc=@clmc
select @feiyong_z=feiyong_z, @feiyong_xf=feiyong_xf, @feiyong_sy=feiyong_sy from gongcheng xinxi where dwgcbh=@dwbh

set @dj_1=convert(money ,@dj)
if @dj_1 0
begin
set @feiyong_xf=@feiyong_xf+@dj_1
set @feiyong_sy=@feiyong_sy-@dj_1

update ly_tzk set syf=@ dj where wtbh=@wtbh
update gongchengxinxi set feiyong_xf=@feiyong_xf, feiyong_sy=@feiyong_sy where dwgcbh=@dwbh
end
else update ly_tzk set syf=convert(char , 0.0) where wtbh=@wtbh

end

//////////////////////

CREATE TRIGGER [TRIGGER ly_tzk_syf_shanchu] ON dbo.ly_tzk
FOR delete
AS
begin
@clmc を宣言しますchar(100) declare @dwbh char(100) declare @dwgcbh char(100) declare @wtbh char(50)
declare @feiyong_z money declare @feiyong_xf money declare @feiyong_ sy money
declare @syf char(100) declare @syf_1 money
--declare @dj char(20) declare @dj_1 money
select @wtbh=wtbh , @clmc=clmc , @dwbh=dwbh ,@syf=syf from inserted
--select @ dj=dj from feihao_bz where clmc= @clmc
select @feiyong_z=feiyong_z、@feiyong_xf=feiyong_xf、@feiyong_sy=feiyong_sy from gongchengxinxi where dwgcbh=@dwbh

set @syf_1 =convert(money,@syf)
if @syf_1 0
begin
set @feiyong_xf=@feiyong_xf-@syf_1
set @feiyong_sy=@feiyong_sy+@syf_1
update gongchengxinxi set feiyong_xf=@ feiyong_xf、feiyong_sy=@feiyong_sy where dwgcbh=@dwbh
end
end