SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF OBJECT_ID(N'dbo.trg_LIMS03T201_ChangeLog', N'TR') IS NOT NULL
BEGIN
    DROP TRIGGER [dbo].[trg_LIMS03T201_ChangeLog];
END
GO
CREATE TRIGGER [dbo].[trg_LIMS03T201_ChangeLog]
ON [dbo].[LIMS03T201]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert
    INSERT INTO [dbo].SourceChangeLog
    (
        SourceTableName,
        SourcePKColumnName,
        SourceEntityID,
        OperationType,
        Status,
        CreatedAt
    )
    SELECT
        N'LIMS03T201',
        N'EquipmentId',
        CAST(i.EquipmentId AS NVARCHAR(255)),
        'I',
        0,
        GETDATE()
    FROM inserted i
    LEFT JOIN deleted d
        ON i.EquipmentId = d.EquipmentId
    WHERE d.EquipmentId IS NULL;

    -- Update
    INSERT INTO [dbo].SourceChangeLog
    (
        SourceTableName,
        SourcePKColumnName,
        SourceEntityID,
        OperationType,
        Status,
        CreatedAt
    )
    SELECT
        N'LIMS03T201',
        N'EquipmentId',
        CAST(i.EquipmentId AS NVARCHAR(255)),
        'U',
        0,
        GETDATE()
    FROM inserted i
    INNER JOIN deleted d
        ON i.EquipmentId = d.EquipmentId
    ;

    -- Delete
    INSERT INTO [dbo].SourceChangeLog
    (
        SourceTableName,
        SourcePKColumnName,
        SourceEntityID,
        OperationType,
        Status,
        CreatedAt
    )
    SELECT
        N'LIMS03T201',
        N'EquipmentId',
        CAST(d.EquipmentId AS NVARCHAR(255)),
        'D',
        0,
        GETDATE()
    FROM deleted d
    LEFT JOIN inserted i
        ON i.EquipmentId = d.EquipmentId
    WHERE i.EquipmentId IS NULL;
END
GO
EXEC [sys].[sp_addextendedproperty] @name = N'MS_Description', @value = N'當來源資料表發生新增、更新、刪除時，自動寫入 SourceChangeLog', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'LIMS03T201', @level2type = N'TRIGGER', @level2name = N'trg_LIMS03T201_ChangeLog';
GO