トリガー作成メモ

TRIGGER作成

基本文法

CREATE TRIGGER [dbo].[TR_TABLE_NAME]
   ON  [dbo].[TABLE_NAME]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here

END
GO

トリガーでのINSERT,UPDATE,DELETEの判定

少し面倒だが、こんな形で判定が可能だった

-- Insert
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN 
    --inserted action
END

-- Update
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
    --updated action
END

-- Delete
IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
BEGIN
    --deleted action
END

トリガーで更新値を変更する

自分自身へのUPDATEを発行する

UPDATE [dbo].[TABLE_NAME]
SET
    [CLM_NAME] = 'ABC'
FROM [dbo].[TABLE_NAME] T
INNER JOIN inserted I
    ON I.KEY = T.KEY