読者です 読者をやめる 読者になる 読者になる

masalibの日記

システム開発、運用と猫の写真ブログです

SQLServerのトリガーを作ってみた

トリガーとは

テーブルのinsertやupdate,Deleteなどの
イベントをトリガーしてSQLを実行できる機能です
今まで作った事がなく
関連するテーブルを一緒に更新する場合は
プログラム側で更新用のSQL用意して対応していました ^^;


概要

参考サイトは履歴テーブルみたいな形だったのですが
自分の場合は検索テーブルだった為
update時にサブ側もupdateしました
その点が違います

実際のSQLの例

--主のテーブル
CREATE TABLE test_main_table
(
     art_id      INT
    ,title    VARCHAR(128)
    ,coldata  VARCHAR(4000)
)
--検索用テーブル
--参考サイトは履歴テーブルだったのですが
--自分の場合は検索用テーブルになります
CREATE TABLE search_sub_table
(
     corner_id CHAR(4)
   , art_id      INT
    ,title    VARCHAR(128)
    ,coldata  VARCHAR(800)
)
--PKやインデックスは省略



トリガーの作成SQLです

CREATE TRIGGER [trigger_test_main_table_insert_update]
  ON [test_main_table] 
    --インサート、アップデートも同じだったのでどちらともにしました
    FOR  INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;

-- SET ANSI_WARNINGSはsubstringで切り落とす時にでるWarningエラーです
SET ANSI_WARNINGS OFF;

-- search_sub_tableに作成および更新するレコードが存在するのかをチェックしています
-- 存在チェックをすることによってinsertもupdateも同じトリガーでいけました
-- SQLのパフォーマンスの観点からみると存在チェックする為、遅くなります。0.1秒単位で早さをもとめるならやめた方がいいかも
-- 昔からあるシステムなので結合するときは照合順序を統一しています

IF (NOT EXISTS (SELECT ts.art_id
                FROM search_sub_table ts , inserted
                WHERE ts.art_id  COLLATE Japanese_CS_AS = inserted.art_id  COLLATE Japanese_CS_AS
               )
   )
-- 存在しないのでインサートする
    INSERT INTO search_sub_table
        ( corner_id, art_id, title , coldata )
    SELECT
        '0001'
      , art_id 
      , title 
      ,SUBSTRING( ( isnull(coldata  ,'')   ,1, 800) 
    FROM inserted
    -- insertedとはインサートしたデータを指します
ELSE
-- 存在するのでUpdateします
   UPDATE search_sub_table
      SET   sst.title = Inst.title
           ,sst.coldata = SUBSTRING( ( isnull(Inst.coldata  ,'')   ,1, 800) 
   FROM search_sub_table sst , inserted Inst
   WHERE  sst.art_id COLLATE Japanese_CS_AS 
       = Inst.article_id COLLATE Japanese_CS_AS
   and corner_id  = '0001'

   -- insertdと書いてありますが更新した内容です
   -- insertdとsearch_sub_tableと結合して更新しています
END


トリガーテスト

 テストはsearch_sub_tableに対してinserとupdateを
 おこなって確認できます
 SQLプロファイルとかでも確認できる思うのですが
 めんどくさいのやっていません

注意点

参考サイトにも記載してありますが

トリガーで実行したSQLでエラーが発生すると
トランザクションの状況によってはロールバックが発生します

自分はパフォーマンスが少し悪くなっても
チェックして必ず通るSQL発行するべきかなと思いました

作ってみて思ったこと

古いサイトの場合だと
全体の更新履歴とかがありません
対象のコーナーテーブルにトリガーをいれて
更新履歴などをつくる事ができると思った
まぁ~工数次第かな・・・



追記

2016/02/05にトリガーのSQL
「SET ANSI_WARNINGS OFF」を追加しました
(これがないと文字数を超えた時にエラーになります)


参考サイト
トリガー作成のSQLなど記載していてわかりやすかったです
SQLServerでトリガーを扱ってみる

Microsoftのリファレンスサイト
https://technet.microsoft.com/ja-jp/library/ms175521(v=SQL.105).aspx