如何跟踪列中发生的更改数量?T-SQL - SQL Server [英] How to track number of changes occured in a column? T-SQL - SQL Server

问题描述

例如，我有一个名为 EmployeeName 的列.

For example, i have a column named EmployeeName.

每次用户更改/修复他的名字时，我都需要计数.如果他更改了两次姓名，则计数为 2.此外，我需要存储员工对 EmployeeName 所做的每次更改的时间，例如如果名称本质上是 James 并且创建的时间是上午 9:00，然后员工在上午 11:00 更改为 John，则我需要保留这个新时间和新值以及之前的 James 在上午 9:00.

Every time a user changes/fix his name, i need to keep a count. If he changes his name twice, then count is 2. Also, i need to store the time of every change employee makes on EmployeeName e.g. if the name essentially is James and time created is 9:00 AM and then employee changes to John on 11:00 AM, i need to preserve this new time and new value as well as the previous one which is James at 9:00 AM.

我希望它清楚！谢谢大家...最佳答案将被选择...

I hope its clear! Thank you guys...Best Answer will be chosen...

如果这需要触发器，非常感谢提供粗略的伪代码.

If this requires a trigger, giving a sketchy pseudo-code will be very much appreciated.

推荐答案

首先 - 如果尚未实施，强烈建议让员工通过永久标识符(即 NOT EmployeeName)进行标识，这样您就可以跟踪所有事情.

First up - if not already implemented, highly advisable to have employees identified by a permanent identifier (i.e. NOT EmployeeName) and so you can keep track on everything.

如果要使用触发器，可以使用 AFTER UPDATE 触发器并使用 if update(EmployeeName) 查找名称的更改.

If you want to use a trigger, you could use an AFTER UPDATE trigger and look for a change to the name using if update(EmployeeName).

如果已更新，您可以同时增加 Employee 表上的 count 列.使用 inserted 表来标识那些已更新的条目.(计数是否必不可少?如果您要存储名称更改的历史记录，我认为没有必要设置计数列 - 这是多余的信息.)

If that's been updated, you could increment the count column on the Employee table at the same time. Use the inserted table to identify those entries which have been updated. (Is the count essential? If you are storing a history of the name changes, I don't think it's necessary to have a count column - it's redundant informaiton.)

然后，您将在员工姓名历史记录表中添加一行，其中包含具有当前时间戳的此更改的详细信息.

You would then add a row to your employee name history table that holds the details of this change with the current timestamp.

这篇关于如何跟踪列中发生的更改数量?T-SQL - SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！