CODEWITHSUNDEEP
sql-servertriggers
Saidi Pindi
Saidi Pindi

Reputation: 13

Collecting updated values in a table using trigger

I have a situation, have a table of around 30+ columns created a audit table with same number of columns and few more additional columns as description, updated date kind of columns. need a trigger to collect updated columns and collect them as description and need to form a sentence like so and so fields are updated into audit table. Help with sample trigger will be appreciated. Thanks in advance..

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

Expecting a sample trigger with a new column in audit table as description which will form a simple sentence to display to users.

Upvotes: 1

Views: 31

Answers (1)

Serg
Serg

Reputation: 22811

The minor problem is comparing nullable columns. It can be done with an expression ISNULL(NULLIF(i.Col, d.Col), NULLIF(d.Col, i.Col)) IS NOT NULL which is true if inserted and deleted row differs on 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

;

Upvotes: 1

Related Questions