当另一个字段发生实际变化时，sql server触发更新时间字段 [英] sql server trigger to update time field when there is real change in another field

问题描述

如果该行的一个或多个字段已更新，我需要一个触发器来更新表的行字段。

I need a trigger that updates a table row field if one or more fields of that row is updated.

假设您有一个Employees表，该表可能如下所示：

Suppose you have an Employees table that may look as follows:

EmployeeId    Name       Address                ModificationDate
1             Spears     27 Sober Road 
2             Jagger     65 Straight Street 

如果除 EmployeeId 和 ModificationDate 字段，触发器应生成一个时间值并更新 ModificationDate 。

If there is a real change in the value of any field except the EmployeeId and ModificationDate fields, the trigger should generate a time value and update the ModificationDate.

实际更改的示例1：

update dbo.Employees
set Name = 'Beggar'
where EmployeeId = 2

示例2，无实际变化：

Example 2 of no real change:

update dbo.Employees
set Name = 'Jagger'
where EmployeeId = 2

如果执行示例2中的更新，则触发er不应更新ModificationDate字段。

If an update in Example 2 executes, the trigger should not update the ModificationDate field.

推荐答案

在触发器中，您可以访问插入和删除系统表。
这些表包含表中已由引起触发器执行的语句更新的记录。

In a trigger, you have access to the 'inserted' and 'deleted' system tables. Those tables contains the records in the table that have been updated by the statement that caused the trigger to execute.

对于 UPDATE触发器，已插入表包含了处于新状态的记录，已删除表包含了具有旧值的记录。

For an 'UPDATE' trigger, the 'inserted' table contains the records like they are in the new state, the 'deleted' table contains the records with the old values.

您必须使用这2个表可以找出哪些记录确实发生了更改，并为这些记录更新ModificationDate。

You'll have to make use of those 2 tables to find out which records have really changed, and update the ModificationDate for those records.

我认为触发器内的语句看起来像这样。 （我尚未测试过）

I think the statement inside the trigger will look something like this. (I haven't tested it)

UPDATE myTable
SET ModificationDate = getdate()
FROM inserted, deleted
WHERE inserted.EmployeeId = deleted.EmployeeId
AND (inserted.Name <> deleted.Name OR inserted.Address <> deleted.Address)

---

编辑：
我玩了一段时间：

---

I've played around a bit:

create trigger upd_employee on [employee] after update
as
begin

    update employee
    set modifdate = getdate()   
    where employee.empid in 
    ( select i.empid
      from inserted i
      inner join deleted d on i.empid = d.empid 
      where (i.name <> d.name or i.address <> d.address )
    )
end

insert into employee 
values
(1, 'Frederik' , '', null)

insert into employee
values
(2, 'User', '', null)

update employee
set [address] = 'some address'

select * from employee

update employee set [name] = 'test' where empid = 2

select * from employee

update employee set [name] = 'test' where empid = 2

select * from employee

这篇关于当另一个字段发生实际变化时，sql server触发更新时间字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！