Trigger UPDATE,INSERT,DELETE

資料更改時同時寫入另一個表

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [ TRIGGER名稱 ]   ON [ 主TABLE] AFTER UPDATE,INSERT,DELETE

AS 

BEGIN

IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)

BEGIN

INSERT INTO  [ 寫入的TABLE] SELECT 'UPDATE',* FROM inserted

END

ELSE IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)

INSERT INTO [ 寫入的TABLE] SELECT 'INSERT',* FROM inserted

ELSE IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted) 

INSERT INTO [ 寫入的TABLE]   SELECT 'DELETE',* FROM deleted

END
USE [dbMES] GO /****** Object: Trigger [dbo].[tb_recordslog_Modify] Script Date: 2020/4/30 上午 09:00:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <KIRA> -- Create date: <20160727> -- Description: <Device setting> -- ============================================= ALTER TRIGGER [dbo].[tb_recordslog_Modify] on [dbo].[tb_recordslog] AFTER INSERT AS BEGIN --表格異動資料時會產生暫存的inserted和deleted兩個表格 --兩個表格格式資訊皆與原表格相同 --inserted紀錄insert資料、update後資料 --deleted紀錄delete資料、update前資料 --依據異動方式將異動資料新增到記錄檔 --inserted和deleted皆有資料表示為-UPDATE --inserted有資料deleted無資料表示為-INSERT IF EXISTS (select 1 from inserted) and Not EXISTS (select 1 from deleted) insert into tb_recordslog_1 select DID,DIP,SID,DVALUE,SYSTIME from inserted END