| MSDE FunClub |
|
最終更新日 : 2000/08/17 |
|
Microsoft Data Engine FunClub
|
Since 2000.08.17
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第６章３６６ｐ 〜 ３７３ｐ掲載】 |
-- [SQL06_05.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- 外部参照のリレーションシップが定義されたテーブル間の
-- 連鎖更新・連鎖削除を実行するものです
--
-- カレントデータベースを 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
-- 主キー側（１側）の既存テーブルが存在したら削除します
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 char(4) CONSTRAINT PK_T_主側 PRIMARY KEY ,
DATA varchar(20)
)
-- **************************************
-- 参照側（多側）テーブルを作成する
-- **************************************
CREATE TABLE T_多側 (
ID2 char(4) CONSTRAINT PK_T_多側 PRIMARY KEY ,
ID char(4) ,
DATA varchar(20)
-- 参照関係を定義する
CONSTRAINT REF1 FOREIGN KEY( ID ) REFERENCES T_主側( ID )
)
-- 外部キーにインデックスを設定する
CREATE INDEX IDX_T_多側_ID ON T_多側 ( ID )
-- REF1リレーションを無効化します
-- INSERT と UPDATE に対するリレーションを無効にする
-- この命令は、参照整合性を SQLServer側ではチェックしないことを要求します
-- そのかわりトリガで管理します
ALTER TABLE T_多側 NOCHECK CONSTRAINT REF1
GO
-- *************************************************
-- 連鎖更新トリガの作成
-- *************************************************
CREATE TRIGGER TR_連鎖更新 -- トリガの名前
ON T_主側 -- トリガを組み込むテーブル
-----------
FOR UPDATE
-----------
AS
-- ID列の更新がありましたか？
-- 更新が発生したら、多側テーブルも修正します
IF UPDATE(ID)
BEGIN
-- 更新対象のレコード数は、１レコード分ですか？
-- １レコードの時は簡単です
IF( (SELECT COUNT(inserted.ID) FROM inserted) = 1 )
BEGIN
-- 多側テーブルの連鎖更新
--PRINT '１レコード分の連鎖更新処理'
UPDATE T_多側
SET T_多側.ID = inserted.ID -- 更新後の値を設定する
-- 交差結合（直積 CROSS JOIN）形式ですが inserted と deleted は
-- １レコ−ドしかないので、直積表のレコード数は T_多側 と一致する
FROM T_多側 , inserted , deleted
WHERE T_多側.ID = deleted.ID -- 更新前の値を検索する
END
ELSE
BEGIN
-- 【複数レコードの一括連鎖更新】
--PRINT '複数レコード分の連鎖更新処理'
-- inserted と deleted の各行がお互い対応します
-- 表を結合させて、SQL文で更新させる
-- このような命令を作ることはできません
-- FROM inserted , deleted
-- のFROM句は、複数レコード間の直積になります
-- insertedやdeletedをシステムが作成するときに、シーケンシャルな
-- 行番号を振ってくれる機能があれば、その番号で等結合できるのに。
-- しかたないので、最後の手段のカーソルを使います
DECLARE @id_ins char(4) , @id_del char(4)
--inserted用
DECLARE INSERT_CUR INSENSITIVE CURSOR
FOR SELECT ID FROM inserted
FOR READ ONLY
--deleted用
DECLARE DELETE_CUR INSENSITIVE CURSOR
FOR SELECT ID FROM deleted
FOR READ ONLY
--カーソルを開く
OPEN INSERT_CUR
OPEN DELETE_CUR
--先頭レコードの読み出し
FETCH NEXT FROM INSERT_CUR INTO @id_ins
FETCH NEXT FROM DELETE_CUR INTO @id_del
--レコードの存在する間、繰り返す
--（２つの表はまったく同じレコード数）
WHILE( @@FETCH_STATUS = 0 )
BEGIN
-- id_del の値が id_ins に変更となりました
-- 主キー１個分の連鎖更新処理
UPDATE T_多側
SET T_多側.ID = @id_ins -- 更新後の値を設定する
WHERE T_多側.ID = @id_del -- 更新前の値を検索する
-- 次のレコードへ
FETCH NEXT FROM INSERT_CUR INTO @id_ins
FETCH NEXT FROM DELETE_CUR INTO @id_del
END
--カーソルを閉じる
CLOSE INSERT_CUR
CLOSE DELETE_CUR
--カーソルを破棄する
DEALLOCATE INSERT_CUR
DEALLOCATE DELETE_CUR
END
END
RETURN
GO
-- *************************************************
-- 連鎖削除トリガの作成
-- 複数レコード処理に対応(deleted)
-- *************************************************
CREATE TRIGGER TR_連鎖削除 -- トリガの名前
ON T_主側 -- トリガを組み込むテーブル
-----------
FOR DELETE
-----------
AS
DELETE T_多側
FROM T_多側 , deleted
WHERE T_多側.ID = deleted.Id -- 削除IDと一致する部分の削除
GO
-- *************************************************
-- 参照整合性の維持トリガの作成
-- 複数レコード操作には対応していません
-- insertedテーブルは１レコード限定です
-- *************************************************
CREATE TRIGGER TR_参照維持 -- トリガの名前
ON T_多側 -- トリガを組み込むテーブル
-----------
FOR INSERT , UPDATE
-----------
AS
DECLARE @ID char(4)
-- ID列の更新ですか？
IF UPDATE(ID)
BEGIN
-- TR_連鎖更新トリガ の中のUPDATE文から呼び出される
-- 主キー側の変更が発生すると必ず呼ばれる
-- [T_多側]テーブルの外部キーに登録されていない主キーの値が変更
-- された場合は、insertedテーブルにレコードはない
IF (SELECT COUNT(ID) FROM inserted) = 0 RETURN
-- [主キー側ID] [iserted.ID]
-- 001 001
-- NULL 002 <= 違反
-- 003 003
-- NULL 004 <= 違反
--
-- このように、insertedテーブルのID列を全部表示して、それに対応する
-- 主キー側のID列を付き合わせて、対応しないNULL値があったら、
-- 参照整合性違反になる
IF EXISTS(
SELECT T_主側.ID , inserted.ID FROM T_主側
RIGHT OUTER JOIN inserted
ON T_主側.ID = inserted.ID
WHERE(T_主側.ID IS NULL )
)
BEGIN
DECLARE @msg varchar(256) , @FK char(4)
SELECT @msg = '(T_多側)入力で参照整合性違反：'
-- 参照整合性違反を検出しました
-- 親切なメッセージを作成します
DECLARE TRI_CUR INSENSITIVE CURSOR
FOR SELECT inserted.ID FROM T_主側
RIGHT OUTER JOIN inserted ON T_主側.ID = inserted.ID
WHERE(T_主側.ID IS NULL )
FOR READ ONLY
--カーソルを作成する
OPEN TRI_CUR
--先頭レコードを取得する
FETCH NEXT FROM TRI_CUR INTO @FK
--レコードの存在する間、繰り返す
WHILE( @@FETCH_STATUS = 0 )
BEGIN
--出力用文字列の作成
SELECT @msg = @msg + '[' + @FK + ']'
-- 次のレコードを取得
FETCH NEXT FROM TRI_CUR INTO @FK
END
SELECT @msg = @msg + '：この値が主キー側で未登録です'
--カーソルを閉じる
CLOSE TRI_CUR
--カーソルを破棄する
DEALLOCATE TRI_CUR
--入力をロールバックする
ROLLBACK TRANSACTION
--エラーメッセージの出力
RAISERROR('%s',16,1,@msg)
RETURN
END
END
GO
--///////////////////////////////////
-- テスト
--///////////////////////////////////
--主キー側の値を登録する
INSERT INTO T_主側 VALUES( '1' , '001' )
INSERT INTO T_主側 VALUES( '2' , '002' )
INSERT INTO T_主側 VALUES( '3' , '003' )
INSERT INTO T_主側 VALUES( '4' , '004' )
INSERT INTO T_主側 VALUES( '5' , '005' )
GO
--多側テーブルにレコードを一括登録する
CREATE TABLE #TMP_T_多側 ( ID char(4) , ID2 char(4) , DATA varchar(20) )
INSERT INTO #TMP_T_多側 VALUES( '1' , '1' , '001' )
INSERT INTO #TMP_T_多側 VALUES( '2' , '1' , '001' )
INSERT INTO #TMP_T_多側 VALUES( '3' , '2' , '002' )
INSERT INTO #TMP_T_多側 VALUES( '4' , '2' , '002' )
INSERT INTO #TMP_T_多側 VALUES( '5' , '3' , '003' )
GO
INSERT INTO T_多側 SELECT * FROM #TMP_T_多側
GO
IF( @@error = 0 )
BEGIN
PRINT '(1)挿入処理は正常終了です'
END
SELECT * FROM T_多側
GO
DELETE FROM T_多側
DELETE FROM #TMP_T_多側
GO
-- ****************************************
-- レコード挿入時に参照整合性違反の発生
-- ****************************************
INSERT INTO #TMP_T_多側 VALUES( '1' , '1' , '001' )
INSERT INTO #TMP_T_多側 VALUES( '2' , '8' , '008' ) -- 違反!!
INSERT INTO #TMP_T_多側 VALUES( '3' , '2' , '002' )
INSERT INTO #TMP_T_多側 VALUES( '4' , '9' , '009' ) -- 違反!!
INSERT INTO #TMP_T_多側 VALUES( '5' , '10' , '003' ) -- 違反!!
INSERT INTO T_多側 SELECT * FROM #TMP_T_多側
GO
IF( @@error = 50000 )
BEGIN
PRINT '(2)挿入処理は失敗しました'
END
GO
-- ****************************************
-- 連鎖更新のテスト
-- ****************************************
DELETE FROM #TMP_T_多側
INSERT INTO #TMP_T_多側 VALUES( '1' , '1' , '001' )
INSERT INTO #TMP_T_多側 VALUES( '2' , '1' , '001' )
INSERT INTO #TMP_T_多側 VALUES( '3' , '2' , '002' )
INSERT INTO #TMP_T_多側 VALUES( '4' , '2' , '002' )
INSERT INTO #TMP_T_多側 VALUES( '5' , '3' , '003' )
INSERT INTO #TMP_T_多側 VALUES( '6' , '4' , '004' )
INSERT INTO #TMP_T_多側 VALUES( '7' , '5' , '005' )
INSERT INTO T_多側 SELECT * FROM #TMP_T_多側
PRINT '連鎖更新前'
SELECT * FROM T_多側
--主キーの変更
UPDATE T_主側
SET ID = 'A'+ID
WHERE( ID IN ( '1' , '3' , '5' ) )
GO
PRINT '連鎖更新後(1,3,5が、A1,A3,A5に変更)'
SELECT * FROM T_多側
GO
-- ****************************************
-- 連鎖削除のテスト
-- ****************************************
--主キーの削除
DELETE T_主側
WHERE( ID IN ( 'A1' , 'A3' , 'A5' ) )
PRINT '連鎖削除後(A1,A3,A5を削除)'
SELECT * FROM T_多側
GO
-- ローカル一時テーブルの削除
DROP TABLE #TMP_T_多側
GO
