Find multiple updated columns in a trigger

Question

I need to define several column updates in a table and save them in the audit table.
Here is the audit scheme:

 auditid   auditTimestamps updateFieldName  oldFieldValue  newFieldValue

We have another table employee with a schema:

  EmpID  EmpName  Age  Address1  Phone

I wrote an update trigger for the employee schema, when the record is updated, the trigger is started, and in the trigger I check what field value has been updated, and save this information in the audit table schema.

 For checking field for update , i have used below code sample:

    BEGIN 
           if update(empName)
           begin 
              set @updatedcolumnname='empName';
              set @newvendorname=(SELECT empNameFROM inserted);
              set @oldvendorname=(SELECT empNameFROM deleted);
              //Here insert logic that insert above find values into audit table.
            end

              // Same check for all remaining fields( Age,Address1,Phone) 
   END

But for me it does not work. it inserts the entire column, which is updated and not updated in the audit table.

Please suggest how you can implement the above functionally.

thank

Answer 1

merge insert . , :

SELECT 'empName' AS FieldName, d.empName AS OldValue, i.empName AS NewValue
FROM Inserted i
INNER JOIN deleted d ON d.EmpID = i.EmpID  
WHERE i.EmpName<>d.ImpName

UNION ALL

SELECT 'Age', d.Age, i.Age
FROM Inserted i
INNER JOIN deleted d ON d.EmpID = i.EmpID
WHERE i.Age<>d.Age

UNION ALL

SELECT 'Address1', d.Address1, i.Address1
FROM Inserted i
INNER JOIN deleted d ON d.EmpID = i.EmpID
WHERE i.Address1<>d.Address1

UNION ALL

SELECT 'Phone', d.Phone, i.Phone
FROM Inserted i
INNER JOIN deleted d ON d.EmpID = i.EmpID
WHERE i.Phone<>d.Phone

, :

MERGE AuditTable AS Destination
USING (
        SELECT 'empName' AS FieldName, d.empName AS OldValue, i.empName AS NewValue
        FROM Inserted i
        INNER JOIN deleted d ON d.EmpID = i.EmpID
        WHERE i.EmpName<>d.ImpName

        UNION ALL

        SELECT 'Age', d.Age, i.Age
        FROM Inserted i
        INNER JOIN deleted d ON d.EmpID = i.EmpID
        WHERE i.Age<>d.Age

        UNION ALL

        SELECT 'Address1', d.Address1, i.Address1
        FROM Inserted i
        INNER JOIN deleted d ON d.EmpID = i.EmpID
        WHERE i.Address1<>d.Address1

        UNION ALL

        SELECT 'Phone', d.Phone, i.Phone
        FROM Inserted i
        INNER JOIN deleted d ON d.EmpID = i.EmpID
        WHERE i.Phone<>d.Phone

    ) AS SOURCE ON SOURCE.FieldName = Destination.UpdateFieldName 
                AND SOURCE.OldValue = Destination.OldFieldValue
                AND SOURCE.NewValue = Destinatino.NewFieldValue
    WHEN MATCHED THEN UPDATE SET OldFieldValue  = OldValue,
                                NewFieldValue = NewValue
    WHEN NOT MATCHED THEN INSERT (UpdateFieldName, OldFieldValue, NewFieldValue) 
                        VALUES(FieldName, OldValue,NewValue)