| MSDE FunClub |
|
最終更新日 : 2000/08/17 |
|
Microsoft Data Engine FunClub
|
Since 2000.08.17
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第６章３３６ｐ 〜 ３３９ｐ掲載】 |
-- [SQL06_01.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- deletedテーブルにカーソルを設定し、１レコード単位
-- でその調査を行う例題です
--
-- カレントデータベースを MySampleTest にする
USE MySampleTest
GO
-- (件処理されました)のメッセージを抑止する
SET NOCOUNT ON
GO
-- *************************************************
-- 見本テーブルの作成
-- *************************************************
-- 既存テーブルが存在したら削除します
IF( (object_id('TRI_TEST') IS NOT NULL) AND
OBJECTPROPERTY( object_id('TRI_TEST'),'ISTABLE') = 1 )
BEGIN
-- PRINT 'テーブルが存在したので削除しました'
DROP TABLE TRI_TEST
END
GO
-- 新しくテーブルを作成する
CREATE TABLE TRI_TEST (
-- 主キー
ID int PRIMARY KEY ,
-- 適当なデータです
DATA varchar(10) ,
)
GO
-- *************************************************
-- トリガの登録
-- *************************************************
CREATE TRIGGER TR_TEST -- トリガの名前
ON TRI_TEST -- トリガを組み込むテーブル
-----------
FOR DELETE,UPDATE
-----------
AS
-- （件処理しました）のメッセージを抑止する
SET NOCOUNT ON
DECLARE @cnt int , @id int , @data varchar(10)
DECLARE @flag int
DECLARE @msg varchar(256)
SELECT @cnt = 0 , @flag = 0
-- 削除や更新されたレコード数を取得する
SELECT @cnt = COUNT(*) FROM deleted
-- 削除・更新対象レコードが２レコード以上ありますか？
-- 複数レコードのときは、カーソルを使います
IF( @cnt>1 )
BEGIN
--削除前・更新前のレコード調査を行なうために
--deletedテーブルに対してカーソルを定義
DECLARE TRI_CUR INSENSITIVE CURSOR
FOR SELECT * FROM deleted
FOR READ ONLY
--カーソルを開く
OPEN TRI_CUR
--更新前の値を取得し、文字列を作成する
RAISERROR('トリガdeletedの主キーの値を出力します',0,1)
SELECT @msg = ''
--先頭レコードを取得する
FETCH NEXT FROM TRI_CUR INTO @id , @data
--レコードの存在する間、繰り返す
WHILE( @@FETCH_STATUS = 0 )
BEGIN
--出力用文字列の作成
SELECT @msg = @msg + '[' + CAST(@id AS char(2)) + ']'
-- ID=2 のレコードの更新・削除は認めません
IF( @id = 2 ) SELECT @flag = 1
-- 次のレコードを取得
FETCH NEXT FROM TRI_CUR INTO @id , @data
END
--メッセージの出力
RAISERROR('%sが更新対象です',0,1,@msg) WITH NOWAIT
--カーソルを閉じる
CLOSE TRI_CUR
--カーソルを破棄する
DEALLOCATE TRI_CUR
--ID=2がありましたか？
IF( @flag = 1 )
BEGIN
-- 変更操作は認めません
ROLLBACK TRANSACTION
-- エラーメッセージを出力します
-- ここでは第２引数を0にしていますが、一般的には16にしてください
RAISERROR('ID=2の削除・更新は一切認めません!!',0,1) WITH NOWAIT
END
-- トリガを終了します
RETURN
END
--///////////////////////
-- １レコード操作の場合
--///////////////////////
SELECT @id=ID , @data=DATA from deleted
RAISERROR('[**１レコード操作**]トリガdeletedの主キーの値 = %d',0,1,@id) WITH NOWAIT
IF( @id = 2 )
BEGIN
-- 変更操作は認めません
ROLLBACK TRANSACTION
-- エラーメッセージを出力します
-- ここでは第２引数を0にしていますが、一般的には16にしてください
RAISERROR('ID=2の削除・更新は一切認めません!!',0,1) WITH NOWAIT
END
RETURN
GO
-- *************************
-- データ登録処理の実行
-- *************************
INSERT INTO TRI_TEST VALUES( 1 , 'A1' )
INSERT INTO TRI_TEST VALUES( 2 , 'A2' )
INSERT INTO TRI_TEST VALUES( 3 , 'B1' )
INSERT INTO TRI_TEST VALUES( 4 , 'B2' )
SELECT * FROM TRI_TEST
GO
-- *************************
-- トリガの起動
-- *************************
--複数レコード同時操作
UPDATE TRI_TEST
SET DATA = 'C' + CAST(ID AS char(1))
WHERE data like 'B%'
SELECT * FROM TRI_TEST
GO
--このUPDATE文は、ID=2 を含むため、認められません
--複数レコード同時操作
UPDATE TRI_TEST
SET DATA = 'D' + CAST(ID AS char(1))
WHERE data like 'A%'
GO
--このUPDATE文は、ID=2 を含むため、認められません
--１レコード操作
UPDATE TRI_TEST
SET DATA = 'E2'
WHERE( ID = 2 )
GO
