概要
以下記事の補足というか、タイトル部分で痛い目を見たので専用記事。
https://qiita.com/so_nkbys/items/67f5be1049ab09618aca
そのまんま、ORACLEのトリガー属性"Before"をSQLServerで実装した際のメモ。
環境
以下の環境とする。
テーブル名:table1
カラム内容
名称 | 型 | その他 |
---|---|---|
ID | int | PK |
Name | Varchar(50) | not null |
tips | Varchar(50) |
コード
Create_Tg01_table1.sql
Create trigger tg01_table1 on table1
instead of insert, update ,delete as
begin
-- Table1PK変数群
declare @ID int;
-- カーソル
declare oldRecords cursor for select ID from deleted;
--declare newRecords cursor for select * from inserted;
-- レコード削除フラグ
declare @IsDelete bit;
set @IsDelete = 0;
-- レコード挿入フラグ
declare @IsInsert bit;
set @IsInsert = 0;
-- トリガー処理必須実行フラグ
-- 0:更新レコードがない場合は処理を行わない(ORACLEのトリガーと同じ動作)
-- 1:更新レコードがなくてもトリガーの処理は行う
declare @IsExecTrigger bit;
set @IsExecTrigger = 0
-- 処理区分判定
if Exists(select * from deleted)
begin
set @IsDelete = 1;
end
if Exists(select * from inserted)
begin
set @IsInsert = 1;
end
-- 変更がない場合は終了
if @IsDelete = 0 and @IsInsert = 0 and @IsExecTrigger = 0
return;
-- この辺りでトリガーにてやりたいことを記述
-- 変更がない場合は終了
if @IsDelete = 0 and @IsInsert = 0
return;
-- 実際行われるはずだった処理の実行(更新は削除→挿入で実行)
-- 削除
if @IsDelete = 1
begin
open oldRecords;
while (1 = 1)
begin
Fetch next from oldRecords into @ID;
-- Fetchが最終行の場合はループを抜ける
if @@FETCH_STATUS <>0
break;
-- 削除実行
delete from table1 where id = @ID;
end
-- 資源開放
CLOSE oldRecords;
DEALLOCATE oldRecords;
end
--挿入
if @IsInsert = 1
insert into table1 select * from inserted;
END
動作確認コード
Test.sql
delete table1;
insert into table1 (id, name, tips) values (100, '蓮巳 敬人', '生徒会副会長');
insert into table1 (id, name, tips) values (200, '来栖 真琴', 'OSIRIS BASE');
insert into table1 (id, name, tips) values (300, '赤ずきん', 'かわいい');
insert into table1 (id, name, tips) values (623, '鴫野睦', 'むつ1むつ2');
update table1 set Name = 'むつみさんは' , tips = 'すごくかわいい'
update table1 set tips = 'むつみさん' where id = 623;
delete table1 where id = 300;
insert into table1 (id, name, tips) values (101, '蓮巳 敬人2', '割とポンコツ');
insert into table1 (id, name, tips) values (201, '来栖 真琴2', 'やっぱりポンコツ');
insert into table1 (id, name, tips) values (301, '赤ずきん2', 'フードどこ行ったかわいい');
insert into table1 (id, name, tips) values (6233, '鴫野睦', 'むつ1むつ2');
select * from table1;
結果
ID | Name | Tips |
---|---|---|
100 | むつみさんは | すごくかわいい |
200 | むつみさんは | すごくかわいい |
101 | 蓮巳 敬人2 | 割とポンコツ |
623 | むつみさんは | むつみさん |
201 | 来栖 真琴2 | やっぱりポンコツ |
301 | 赤ずきん2 | フードどこ行ったかわいい |
6233 | 鴫野睦 | むつ1むつ2 |
所感
ORACLEでBeforeつけるだけなのにこんなに実装しなきゃいけないとかしんどい。
なお(余談)
当初はnewについてもカーソルを開けてUpdate時はチェック…としようとしてたが全件比較しなきゃいけないことに気づき断念。
あと、更新情報の入るカーソル名の変数名を"newTable"にしてたら延々と処理実行時にエラーを吐かれた。
何かと思ったらこれ、予約語の模様。inserted使う場合はカーソル名に注意。