在触发器中查找多个更新的列

Atul Thakre 发表于 Dev

Atul Thakre

我需要在一个表中标识多个列更新，并将它们存储在Audit表中。
这是审核架构：

 auditid   auditTimestamps updateFieldName  oldFieldValue  newFieldValue

我们还有另一个具有架构的表员工：

  EmpID  EmpName  Age  Address1  Phone

我在员工架构上编写了更新触发器，当记录更新时，触发器被触发，在触发器上，我检查哪个字段值已更新并在审计表架构上维护此信息。

 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

但这似乎对我不起作用。它将所有已更新但未更新的列插入审核表。

请提出如何在功能上实现上述建议。

谢谢

梅迪·洛菲

您可以在触发器中使用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)