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