| MSDE FunClub |
|
最終更新日 : 2000/08/17 |
|
Microsoft Data Engine FunClub
|
Since 2000.08.17
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第６章３５４ｐ 〜 ３５７ｐ掲載】 |
-- [SQL06_03.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- トリガを利用して在庫個数と販売個数を管理する例題です。
-- 商品を販売したら在庫個数を減らします。ただ在庫個数が
-- 負になる場合の入力はトリガで違反とします。
-- この例題は、簡単に考えるため１レコード単位で更新命令
-- が発行されるものとする
--
-- 【注意】
-- ROLLBACKとRAISERRORを実行するときは、ROLLBACKを先に
-- 実行します
-- エラーメッセージ番号を伝播させるためです
-- ROLLBACKが行われると @@ERRORは 0 に戻されます
--
-- カレントデータベースを MySampleTest にする
USE MySampleTest
GO
-- (件処理されました)のメッセージを抑止する
SET NOCOUNT ON
GO
-- *************************************************
-- 見本テーブルの作成
-- *************************************************
-- 既存テーブルが存在したら削除します
IF( (object_id('T_在庫') IS NOT NULL) AND
OBJECTPROPERTY( object_id('T_在庫'),'ISTABLE') = 1 )
BEGIN
PRINT 'T_在庫 テーブルが存在したので削除しました'
DROP TABLE T_在庫
END
GO
-- 新しくテーブルを作成する
CREATE TABLE T_在庫 (
-- 主キー
ID int CONSTRAINT PK_T_在庫 PRIMARY KEY , -- 商品番号
-- 在庫データです
ZAIKO_DATA int
)
GO
-- 既存テーブルが存在したら削除します
IF( (object_id('T_販売') IS NOT NULL) AND
OBJECTPROPERTY( object_id('T_販売'),'ISTABLE') = 1 )
BEGIN
PRINT 'T_販売 テーブルが存在したので削除しました'
DROP TABLE T_販売
END
GO
-- 新しくテーブルを作成する
CREATE TABLE T_販売 (
-- 主キー（連番）
IDROW int IDENTITY(1,1) CONSTRAINT PK_T_販売 PRIMARY KEY ,
-- 商品番号
ID int ,
-- 販売個数
HANBAI_DATA int
)
GO
-- *************************************************
-- トリガの登録
-- 挿入と更新処理を行う
-- *************************************************
CREATE TRIGGER TR_販売検査 -- トリガの名前
ON T_販売 -- トリガを組み込むテーブル
-----------
FOR UPDATE,INSERT
-----------
AS
DECLARE @入力後個数 int
DECLARE @入力前個数 int
DECLARE @増加 int
DECLARE @ID int
--レベルは１です
--すでにトランザクションが開始されています
--PRINT 'トリガ開始直後のトランザクションレベル = ' + CAST(@@TRANCOUNT AS char)
-- SELECT文のメッセージを抑止する
SET NOCOUNT ON
-- このトリガは１レコードしか対応しません
IF (SELECT COUNT(IDROW) FROM inserted) <> 1
BEGIN
ROLLBACK TRANSACTION -- 変更を元に戻す
RAISERROR('T_販売 テーブルは複数レコード同時操作禁止',16,1)
RETURN -- トリガの終了
END
-- 個数を求める
SELECT @入力前個数 = 0 -- 初期値代入をしておくこと
SELECT @入力前個数 = ISNULL(HANBAI_DATA,0) FROM deleted
SELECT @入力後個数 = ISNULL(HANBAI_DATA,0) ,
@ID = ID FROM inserted
--RAISERROR('@入力前個数=%d @入力後個数=%d @ID=%d' , 0,1 ,
-- @入力前個数 , @入力後個数 , @ID ) WITH NOWAIT
-- IDがT_在庫に登録されていなければ致命的エラーです
IF NOT EXISTS(SELECT ID FROM T_在庫 WHERE ID = @ID)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('ID=%d は、T_在庫 では未登録です', 16,1,@ID)
RETURN
END
-- 個数には変化ありません
IF (@入力前個数 = @入力後個数)
BEGIN
--RAISERROR('入力個数に変化はありませんでした（終了）',0,1) WITH NOWAIT
RETURN
END
-- 在庫個数調査が必要です
IF( @入力前個数 < @入力後個数 )
BEGIN
--PRINT '在庫引き算が必要です'
SELECT @増加 = @入力後個数 - @入力前個数
--RAISERROR('@入力前個数=%d @入力後個数=%d @増加=%d',0,1,
-- @入力前個数,@入力後個数,@増加) WITH NOWAIT
-- @増加分を在庫から試しに引き算します
-- 但しトランザクションを作ります
-- 注意：トリガ内部は暗黙のトランザクションが開始されているので、この
-- BEGIN TRANの命令は不要です
-- 勉強のため、入れました
BEGIN TRANSACTION
UPDATE T_在庫 SET ZAIKO_DATA = ZAIKO_DATA - @増加
WHERE (ID = @ID)
--在庫が負になっていたら、中止する
IF (SELECT ZAIKO_DATA FROM T_在庫 WHERE (ID = @ID) ) < 0
BEGIN
-- 外側のトランザクションまで中止されます
ROLLBACK TRANSACTION
RAISERROR('在庫個数が負になるため、中止します',16,1)
RETURN
END
-- 更新できました
--RAISERROR('在庫個数の更新ができました',0,1)WITH NOWAIT
--BEGIN TRAN 同様、このCOMMIT TRANは不要です
COMMIT TRANSACTION
--PRINT 'トランザクションレベル = ' + CAST(@@TRANCOUNT AS char)
RETURN
END
-- 在庫個数を増やす必要があります
-- 恐らく入力ミスで同じ場所で２回も入力操作を行ったのでしょう
SELECT @増加 = @入力前個数 - @入力後個数
UPDATE T_在庫 SET ZAIKO_DATA = ZAIKO_DATA + @増加
WHERE (ID = @ID)
RETURN
GO
-- テスト ---
INSERT INTO T_在庫 values(1,10)
GO
INSERT INTO T_販売(ID,HANBAI_DATA) values(1,3)
SELECT * FROM T_在庫
GO
