SQLServerのトリガーでOracleのBeforeを疑似的に再現する

概要

以下記事の補足というか、タイトル部分で痛い目を見たので専用記事。
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, '鴫野睦', 'むつ１むつ２');

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, '鴫野睦', 'むつ１むつ２');
select * from table1;

結果

ID	Name	Tips
100	むつみさんは	すごくかわいい
200	むつみさんは	すごくかわいい
101	蓮巳 敬人2	割とポンコツ
623	むつみさんは	むつみさん
201	来栖 真琴2	やっぱりポンコツ
301	赤ずきん2	フードどこ行ったかわいい
6233	鴫野睦	むつ１むつ２

所感

ORACLEでBeforeつけるだけなのにこんなに実装しなきゃいけないとかしんどい。

なお(余談)

当初はnewについてもカーソルを開けてUpdate時はチェック…としようとしてたが全件比較しなきゃいけないことに気づき断念。
あと、更新情報の入るカーソル名の変数名を"newTable"にしてたら延々と処理実行時にエラーを吐かれた。
何かと思ったらこれ、予約語の模様。inserted使う場合はカーソル名に注意。