| MSDE FunClub |
|
最終更新日 : 2000/08/21 |
|
Microsoft Data Engine FunClub
|
Since 2000.08.21
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第８章４６４ｐ 〜 ４６８ｐ掲載】 |
-- [SQL08_03.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- トリガ内部でROLLBACK命令を実行するものです
--
-- この［社員］テーブルは、AccessのNorthwindデモデータベース
-- の［社員］テーブルをSQLServerに転送したものです
--
-- カレントデータベースを MySampleTest にする
USE MySampleTest
GO
-- (件処理されました)のメッセージを抑止する
SET NOCOUNT ON
GO
-- *********************************************************
-- 【社員】テーブルにトリガを組み込む
-- 注意：
-- 1)ROLLBACK命令を実行するときは、RAISERRORでメッセージ
-- を出力すること
-- 2)そのRAISERROR命令は、ROLLBACK命令の後で実行すること
-- 3)外部変数@@ERRORは、直前のSQL文の実行結果を記憶する
-- 4)ROLLBACKを正常に終えると、@@ERRORは 0 になります
-- 5)だから、先にROLLBACK命令を実行して、その後でRAISERROR
-- 命令を実行すれば、@@ERRORに RAISERRORのメッセージ番号
-- を入れることができる
-- 6)この順番を間違えると、ダメ!!
-- *********************************************************
IF( (object_id('TR_氏名CHK') IS NOT NULL) AND
OBJECTPROPERTY(object_id('TR_氏名CHK') ,'IsTrigger' ) = 1 )
BEGIN
DROP TRIGGER TR_氏名CHK
END
GO
-- /////////////////////////////
CREATE TRIGGER TR_氏名CHK -- トリガの名前
-- /////////////////////////////
ON 社員 -- トリガを組み込むテーブル
-----------
FOR INSERT -- 挿入時処理
-----------
AS
SET NOCOUNT ON -- SELECT文のメッセージの抑止
--氏名欄に、'加藤 一郎'を入力したときはROLLBACKを実施する
IF UPDATE(氏名)
BEGIN
IF EXISTS( SELECT 氏名 FROM inserted WHERE 氏名 = '加藤 一郎' )
BEGIN
-- 入力されたので、違反とします
-- 暗黙のトランザクションをロールバックする
ROLLBACK TRANSACTION
--メッセージ出力はROLLBACKの後にすること
--外部@@ERRORに50000番が設定されます
--ROLLBACKの前で実行すると、せっかく設定された50000番が
--ROLLBACK命令自体の正常終了により、0 に戻される
RAISERROR( '加藤一郎さんの入力はできません',16,1)
RETURN
END
END
GO
-- *******************************
-- レコードの登録結果を表示
-- *******************************
IF( (object_id('P_挿入結果表示') IS NOT NULL) AND
OBJECTPROPERTY(object_id('P_挿入結果表示') ,'IsProcedure' ) = 1 )
BEGIN
DROP PROCEDURE P_挿入結果表示
END
GO
-- /////////////////////////////
CREATE PROCEDURE P_挿入結果表示
-- /////////////////////////////
AS
DECLARE @社員コード int , @フリガナ varchar(20) , @氏名 varchar(20)
DECLARE @Str社員コード varchar(4) , @strout varchar(80)
DECLARE hC INSENSITIVE CURSOR FOR
SELECT 社員コード,フリガナ,氏名 FROM 社員
WHERE( 社員コード BETWEEN 10 AND 20 )
-- カーソルを開いてレコードを表示する
OPEN hC
-- レコードはありますか？
IF( @@CURSOR_ROWS = 0 )
BEGIN
PRINT '*** レコードは存在しません ***'
PRINT ' レコード登録に失敗!! '
GOTO L9999
END
-- タイトル行の出力
EXEC master..xp_sprintf @strout OUTPUT , '[%.2s][%12s][%12s]' ,
'NO' , 'フリガナ' , ' 氏 名 '
PRINT ''
PRINT '**** 登録結果 ****'
PRINT @strout
-- 先頭行の取り出し
FETCH NEXT FROM hC INTO @社員コード,@フリガナ,@氏名
-- 0の時は、正常に取得できました
WHILE( @@FETCH_STATUS = 0 )
BEGIN
-- 取り出し内容の編集とその出力
SELECT @Str社員コード = CONVERT(char(4),@社員コード)
EXEC master..xp_sprintf @strout OUTPUT , '[%.2s][%12s][%12s]' ,
@Str社員コード , @フリガナ , @氏名
PRINT @strout
-- 次行の取り出し
FETCH NEXT FROM hC INTO @社員コード,@フリガナ,@氏名
END
L9999:
-- カーソルを閉じて破棄する
CLOSE hC
DEALLOCATE hC
GO
-- *********************************************************
-- 【トリガ違反を何も考慮しない場合】
-- トランザクション内でトリガ違反が発生します
-- *********************************************************
DECLARE @TRI_ERR int
SELECT @TRI_ERR = 1 -- =0:トリガ違反は発生しない
-- =1:トリガ内部でROLLBACKを実行
PRINT ''
PRINT '【トリガ内でROLLBACKが発生します（何も考慮しない場合）】'
-- レコード挿入位置にレコードがあれば削除します
DELETE FROM 社員 WHERE( 社員コード BETWEEN 10 AND 20 )
INSERT INTO 社員(社員コード,フリガナ,氏名)
VALUES(10,'ヤマダ イチロウ' , '山田 一郎')
INSERT INTO 社員(社員コード,フリガナ,氏名)
VALUES(11,'ヤマダ ジロウ' , '山田 二郎')
INSERT INTO 社員(社員コード,フリガナ,氏名)
VALUES(12,'ヤマダ サブロウ', '山田 三郎')
-- １の時は、トリガ内部でROLLBACKが実行されます
IF( @TRI_ERR = 1 ) INSERT INTO 社員(社員コード,フリガナ,氏名)
VALUES(13,'カトウ イチロウ' , '加藤 一郎')
IF( @@ERROR = 0 )
BEGIN
PRINT 'レコード挿入でまったくエラーは起こりませんでした'
EXEC P_挿入結果表示
END
ELSE
BEGIN
--トリガの内部でROLLBACKすると、この命令は実行されない
PRINT 'トリガ内部でROLLBACKを実行すると、そのバッチが強制終了します'
PRINT 'この命令は実行されませんので注意しましょう!!'
PRINT '結果表示を行なう次の命令も実行されません'
END
GO
-- ********************************************************
-- 上記バッチ内でトリガ違反によるROLLBACKは発生しましたか？
-- ********************************************************
-- トリガ内部で発生させたRAISERRORメッセージ番号です
-- 自作メッセージは50000番になります
IF( @@ERROR = 50000 )
BEGIN
PRINT ''
PRINT '*** レコード挿入処理で、トリガ違反が起こりました ***'
-- 登録されたレコードを表示します
EXEC P_挿入結果表示
END
GO
-- *********************************************************
-- 【トリガ違反を考慮する場合】
-- トランザクション内でトリガ違反が発生します
-- *********************************************************
DECLARE @TRI_ERR int
SELECT @TRI_ERR = 1 -- =0:トリガ違反は発生しない
-- =1:トリガ内部でROLLBACKを実行
PRINT ''
PRINT '【トリガ内でROLLBACKが発生します（全体をトランザクションで囲む）】'
-- レコード挿入位置にレコードがあれば削除します
DELETE FROM 社員 WHERE( 社員コード BETWEEN 10 AND 20 )
BEGIN TRANSACTION -- トランザクションの開始
INSERT INTO 社員(社員コード,フリガナ,氏名)
VALUES(10,'ヤマダ イチロウ' , '山田 一郎')
INSERT INTO 社員(社員コード,フリガナ,氏名)
VALUES(11,'ヤマダ ジロウ' , '山田 二郎')
INSERT INTO 社員(社員コード,フリガナ,氏名)
VALUES(12,'ヤマダ サブロウ', '山田 三郎')
IF(@TRI_ERR = 1 ) INSERT INTO 社員(社員コード,フリガナ,氏名)
VALUES(13,'カトウ イチロウ' , '加藤 一郎')
COMMIT TRANSACTION
IF( @@ERROR = 0 )
BEGIN
PRINT 'レコード挿入でまったくエラーは起こりませんでした'
EXEC P_挿入結果表示
END
ELSE
BEGIN
--トリガの内部でROLLBACKすると、この命令は実行されない
PRINT 'トリガ内部でROLLBACKを実行すると、そのバッチが強制終了します'
PRINT 'この命令は実行されませんので注意しましょう!!'
PRINT '結果表示を行なう次の命令も実行されません'
END
GO
-- ********************************************************
-- 上記バッチ内でトリガ違反によるROLLBACKは発生しましたか？
-- ********************************************************
-- トリガ内部で発生させたRAISERRORメッセージ番号です
-- 自作メッセージは50000番になります
IF( @@ERROR = 50000 )
BEGIN
PRINT ''
PRINT '*** レコード挿入処理で、トリガ違反が起こりました ***'
-- 登録されたレコードを表示します
EXEC P_挿入結果表示
END
GO
