トリガー作成メモ
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