TRIGGER SAMPLE

USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tg_NAME]
   ON  [dbo].[TABLE_TAB]
   AFTER INSERT,UPDATE,DELETE
AS
BEGIN
    SET NOCOUNT ON;
  
    IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) = 0 BEGIN --新增
        INSERT INTO TABLE_LOG
        SELECT *, 'INSERT' FROM INSERTED
    END
  
    IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) > 0 BEGIN --變更
        INSERT INTO TABLE_LOG
        SELECT *, 'UPDATE_BEFORE' FROM DELETED
        INSERT INTO TABLE_LOG
        SELECT *, 'UPDATE_AFTER' FROM INSERTED
    END
  
    IF (SELECT COUNT(*) FROM INSERTED) = 0 AND (SELECT COUNT(*) FROM DELETED) > 0 BEGIN --刪除
        INSERT INTO TABLE_LOG
        SELECT *, 'DELETE' FROM DELETED
    END
  
              
END

GO