时间戳

首页 > 解决方案 > 使用触发器收集表中的更新值

sql-server - 使用触发器收集表中的更新值

问题描述

我有一个情况，有一个大约 30 多列的表创建了一个审计表，该表具有相同数量的列和更多的附加列作为描述，更新日期类型的列。需要一个触发器来收集更新的列并将它们收集为描述，并且需要形成一个句子，比如某某字段被更新到审计表中。样品触发器的帮助将不胜感激。提前致谢..

ALTER TRIGGER [dbo].[trg_reservationdetail_audit]
ON [dbo].[tblReservationDetails]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT reservationDetails_audits(
        reservationDetailId,
        reservationId,
        rdCreationDate,
        rdItemTypeId,
        rdDeparture,
        rdArrival,
        rdPax,
        PaxChildren,
        PaxBabies,
        rdStatusId,
        rdIsCancelled,
        rdPackageId,
        rdRateId,
        rdPrice,
        rdTaxId,
        rdRoomId,
        rdTaxAmount,
        rdDays,
        siteId,
        CreatorID,
        CreatorName,
        Updated,
        UpdatedBy,
        Amount,
        Segment_ID,
        Source_ID,
        Remarks,
        SessionId,
        Contact_ID,
        CreatorContactProfileID,
        HotelReservationUniqueID,
        HotelReservationResID_Value,
        RoomStayId,
        ChnMgrContent_ID,
        InvoiceTo,
        SourceContext,
        BlockRoomChange,
        BlockRoomChangeReasonId,
        rdinvoiceid,
        isOnHoldResDet,
        updated_at,
        Operation,
        Description)
     SELECT
       i.reservationDetailId,
        reservationId,
        rdCreationDate,
        rdItemTypeId,
        rdDeparture,
        rdArrival,
        rdPax,
        PaxChildren,
        PaxBabies,
        rdStatusId,
        rdIsCancelled,
        rdPackageId,
        rdRateId,
        rdPrice,
        rdTaxId,
        rdRoomId,
        rdTaxAmount,
        rdDays,
        siteId,
        CreatorID,
        CreatorName,
        Updated,
        UpdatedBy,
        Amount,
        Segment_ID,
        Source_ID,
        Remarks,
        SessionId,
        Contact_ID,
        CreatorContactProfileID,
        HotelReservationUniqueID,
        HotelReservationResID_Value,
        RoomStayId,
        ChnMgrContent_ID,
        InvoiceTo,
        SourceContext,
        BlockRoomChange,
        BlockRoomChangeReasonId,
        rdinvoiceid,
        i.isOnHoldResDet,           
        GETDATE(),
   CASE WHEN EXISTS (SELECT * FROM Deleted) THEN 'UPD' ELSE 'INS' END
FROM
   Inserted I
UNION ALL
SELECT
       d.reservationDetailId,
        reservationId,
        rdCreationDate,
        rdItemTypeId,
        rdDeparture,
        rdArrival,
        rdPax,
        PaxChildren,
        PaxBabies,
        rdStatusId,
        rdIsCancelled,
        rdPackageId,
        rdRateId,
        rdPrice,
        rdTaxId,
        rdRoomId,
        rdTaxAmount,
        rdDays,
        siteId,
        CreatorID,
        CreatorName,
        Updated,
        UpdatedBy,
        Amount,
        Segment_ID,
        Source_ID,
        Remarks,
        SessionId,
        Contact_ID,
        CreatorContactProfileID,
        HotelReservationUniqueID,
        HotelReservationResID_Value,
        RoomStayId,
        ChnMgrContent_ID,
        InvoiceTo,
        SourceContext,
        BlockRoomChange,
        BlockRoomChangeReasonId,
        rdinvoiceid,
        d.isOnHoldResDet,           
        GETDATE(),
        'DEL'
     FROM Deleted d
    WHERE NOT EXISTS (
   SELECT * FROM Inserted
);
END

期望在审计表中有一个新列作为描述的示例触发器，它将形成一个简单的句子来显示给用户。

标签： sql-servertriggers

解决方案

小问题是比较可为空的列。可以使用一个表达式来完成， ISNULL(NULLIF(i.Col, d.Col), NULLIF(d.Col, i.Col)) IS NOT NULL如果inserted和deleted行不同，则该表达式为真Col。

INSERT reservationDetails_audits(
        reservationDetailId,
        reservationId,
        rdCreationDate,
        -- ..
        updated_at,
        Operation,
        Description)
SELECT
        i.reservationDetailId,
        i.reservationId,
        i.rdCreationDate,
        -- ..
        i.isOnHoldResDet,           
        GETDATE(),
        CASE WHEN d.reservationDetailId IS NOT NULL THEN 'UPD' ELSE 'INS' END,
        CASE WHEN d.reservationDetailId IS NOT NULL THEN
          ' updated cols: '
          -- assumming reservationId is not nullable
          + CASE i.reservationId <> d.reservationId THEN 'reservationId ' ELSE '' END 
          -- assumming rdCreationDate is nullable
          + CASE ISNULL(NULLIF(i.rdCreationDate, d.rdCreationDate), NULLIF(d.rdCreationDate, i.rdCreationDate)) IS NOT NULL THEN 'rdCreationDate ' ELSE '' END
          -- + ..
        ELSE '' END
FROM Inserted I
LEFT JOIN deleted d on d.reservationDetailId = i.reservationDetailId
UNION ALL
--  delete oper query

;

推荐阅读