I am currently playing around with the idea of having history tables for some of my tables in my database. Basically I have the main table and a copy of that table with a modified date and an action column to store what action was preformed eg Update,Delete and Insert.
我目前正在考虑在我的数据库中为我的一些表设置历史表。基本上，我有主表和该表的一个副本，其中有一个修改过的日期和一个动作列，用来存储什么动作是预先形成的，例如更新、删除和插入。
So far I can think of three different places that you can do the history table work.
到目前为止，我可以想到三个不同的地方，你可以做历史表的工作。
- Triggers on the main table for update, insert and delete. (Database)
- 主表上的触发器，用于更新、插入和删除。(数据库)
- Stored procedures. (Database)
- 存储过程。(数据库)
- Application layer. (Application)
- 应用程序层。(应用程序)
My main question is, what are the pros, cons and gotchas of doing the work in each of these layers.
我的主要问题是，在每一层中进行工作的优点、缺点和缺点是什么?
One advantage I can think of by using the triggers way is that integrity is always maintained no matter what program is implmentated on top of the database.
通过使用触发器的方式，我可以想到的一个优点是，不管数据库上是什么程序，完整性都始终是保持的。
6 个解决方案
#1
12
I'd put it this way:
我这样说:
- Stored procs: they're bypassed if you modify the table directly. Security on the database can control this
- 存储过程:如果直接修改表，它们将被绕过。数据库上的安全性可以控制这一点
- Application: same deal. Also if you have multiple applications, possibly in different languages, it needs to be implemented in each stack, which is somewhat redundant; and
- 应用范围:同样的协议。如果您有多个应用程序，可能使用不同的语言，那么需要在每个堆栈中实现它，这有点多余;和
- Triggers: transparent to the application and will capture all changes. This is my preferred method.
- 触发器:对应用程序透明，并将捕获所有更改。这是我比较喜欢的方法。
#2
10
Triggers are the quickest and easiest way to achieve simple history. The following information assumes a more complex example where history processing may include some business rules and may require logging information not found in the table being tracked.
触发器是实现简单历史的最快和最简单的方法。下面的信息假设有一个更复杂的示例，其中历史处理可能包含一些业务规则，并且可能需要跟踪表中没有的日志信息。
To those that think that triggers are safer than sprocs because they cannot be bypassed I remind them that they are making the following assumption:
对于那些认为触发因素比sprocs更安全的人，我提醒他们，他们正在做出以下假设:
!) Permissions exist that stop users from executing DISABLE TRIGGER [but then permissions could too exist to limit all access to the database except for EXECUTE on sprocs which is a common pattern for enterprise applications] - therefore one must assume correct permissions and therefore sprocs equal triggers in terms of security and ability to be bypassed
!)权限存在阻止用户执行禁用触发器(但可能也存在限制所有权限访问数据库除了执行存储过程为企业应用程序)是一种常见的模式,因此必须承担正确的权限,因此存储过程平等引发的安全性和能力被忽略
!) Depending on the database it may be possible to execute update statements that do not fire triggers. I could take advantage of knowledge of nested trigger execution depth to bypass a trigger. The only sure solution includes security in database and limiting access to data using only approved mechanisms - whether these be triggers, sprocs or data access layers.
!)根据数据库的不同，可以执行不触发触发器的update语句。我可以利用嵌套触发器执行深度的知识绕过触发器。唯一可靠的解决方案包括数据库中的安全性和仅使用经过批准的机制限制对数据的访问——无论是触发器、sprocs还是数据访问层。
I think the choices are clear here. If the data is being accessed by multiple applications then you want to control the history from the lowest common layer and this will mean the database.
我认为这里的选择很明确。如果数据被多个应用程序访问，那么您希望从最低的公共层控制历史，这将意味着数据库。
Following the above logic, the choice of triggers or stored procedures depends again on whether the stored procedure is the lowest common layer. You should prefer the sproc over the trigger as you can control performance, and side effects better and the code is easier to maintain.
按照上述逻辑，触发器或存储过程的选择再次取决于存储过程是否是最低的公共层。您应该更喜欢sproc而不是触发器，因为您可以更好地控制性能和副作用，并且代码更易于维护。
Triggers are acceptable, but try to make sure that you do not increase locks by reading data outside of the tables being updated. Limit triggers to inserts into the log tables, log only what you need to.
触发器是可以接受的，但是要确保您不会通过读取正在更新的表之外的数据来增加锁。限制触发器插入到日志表中，只记录需要记录的内容。
If the application uses a common logical access layer and it is unlikely that this would change over time I would prefer to implement the logic here. Use a Chain Of Responsibility pattern and a plug-in architecture, drive this from Dependency Injection to allow for all manner of processing in you history module, including logging to completely different types of technology, different databases, a history service or anything else that you could imagine.
如果应用程序使用公共逻辑访问层，而且随着时间的推移，这种情况不太可能改变，我宁愿在这里实现这个逻辑。使用一个职责链模式和一个插件体系结构，从依赖项注入中驱动它，以允许在历史模块中进行所有方式的处理，包括将日志记录到完全不同类型的技术、不同的数据库、历史服务或其他您可以想象的任何东西。
#3
6
Have been using the trigger based approach for years and it has definitely worked well for us, but then you do have the following points to ponder over:
多年来，我们一直在使用基于触发器的方法，它肯定对我们很有效，但是接下来你要考虑以下几点:
-
Triggers on a heavily used (say, a multi-tenant SaaS based application) could be extremely expensive
频繁使用的应用程序(比如基于多租户SaaS的应用程序)的触发器可能非常昂贵
-
In some scenarios, a few fields can get redundant. Triggers are good only when you are crystal clear on the fields to be logged; though using an application you could have an interceptor layer which could help you log certain fields based on the "configuration"; though with it's own share of overheads
在某些场景中，一些字段可能会变得多余。触发器只有当您在要记录的字段上非常清楚时才有效;虽然使用一个应用程序，您可以有一个拦截层，可以帮助您根据“配置”记录某些字段;尽管它也有自己的管理费用
-
Without adequate database control, a person could easily disable the triggers, modify the data and enable the triggers; all without raising any alarms
如果没有足够的数据库控制，一个人可以很容易地禁用触发器、修改数据并启用触发器;所有这些都没有引起任何警报。
-
In case of web applications, where the connections are established from a pool, tracking the actual users who made the changes can be tedious. A possible solution would be to have the "EditedBy" field in every transaction table.
对于从池中建立连接的web应用程序，跟踪做出更改的实际用户可能会很麻烦。一个可能的解决方案是在每个事务表中都有“EditedBy”字段。
#4
5
Late one but it adds couple more options that can be considered.
但它增加了一些可以考虑的选项。
Change Data Capture: This feature is available in SQL Server 2008 R2+ but only in enterprise edition. It allows you to select tables you want to track and SQL Server will do the job for you. It works by reading transaction log and populating history tables with data.
更改数据捕获:此功能在SQL Server 2008 R2+中可用，但仅限于企业版。它允许您选择要跟踪的表，而SQL Server将为您完成任务。它通过读取事务日志和使用数据填充历史表来工作。
Reading transaction log: If database is in full recovery mode then transaction log can be read and details on almost transactions can be found.
读取事务日志:如果数据库处于完全恢复模式，则可以读取事务日志，并找到几乎所有事务的详细信息。
Downside is that this is not supported by default. Options are to read transaction log using undocumented functions like fn_dblog or third party tools such as ApexSQL Log.
缺点是默认情况下不支持。选项是使用未文档化的函数(如fn_dblog)或第三方工具(如ApexSQL log)读取事务日志。
Triggers: Works just fine for small number of tables where there are not too many triggers to manage. If you have a lot of tables you want to audit then you should consider some third party tool for this.
触发器:对于少量没有太多触发器可管理的表来说，这是可以的。如果您有很多表需要审计，那么您应该考虑使用第三方工具进行审计。
All of these work at the database level and are completely transparent to application.
所有这些工作都在数据库级，并且对应用程序完全透明。
#5
2
Triggers are the only reliable way to capture changes. If you do it in Stored Procs or the App, you can always go in and SQL away a change that you don't have a log for (inadvertantly). Of course, somebody who doesn't want to leave a log can disable triggers. But you'd rather force somebody to disable the logging than hope that they remember to include it.
触发器是捕获更改的唯一可靠方法。如果您在存储的Procs或应用程序中执行此操作，您总是可以使用SQL来删除您没有日志记录的更改(无意中)。当然，不想留下日志的人可以禁用触发器。但是，您宁愿强制某人禁用日志记录，也不希望他们记得包含日志记录。
#6
0
Usually if you choose the application layer, you can design your app code to do the logging in a single point, that will handle consistenly all your historical table. differently triggers are a more complicated approach to maintain because they are (depending on the db technology) replicated for every table: in case of hundred of tables the amount of code for the trigger coud be a problem.
通常，如果您选择应用层，您可以设计您的应用程序代码来在一个点上进行日志记录，这将稳定地处理所有历史表。不同的触发器是一种更复杂的维护方法，因为它们(取决于db技术)对每个表都进行了复制:对于数百个表，触发器的代码量是一个问题。
if you have a support organization that will maintain the code you are writing now, and you don't know who will maintain your code (tipical for big industries) you cannot assume which is the skill level of the person who will do fix on your application, in that case it is better in my opinion to make the historical table working principle as simple as possible, and the application layer is probably the best place for this purpose.
如果你有一个支持组织将保持你现在写的代码,和你不知道谁会维护你的代码(典型的大行业)你不能承担的技术水平的人会修复你的应用程序,在这种情况下最好是在我看来,让历史表尽可能的简单工作原理,和应用程序层可能是最好的地方。
#1
12
I'd put it this way:
我这样说:
- Stored procs: they're bypassed if you modify the table directly. Security on the database can control this
- 存储过程:如果直接修改表，它们将被绕过。数据库上的安全性可以控制这一点
- Application: same deal. Also if you have multiple applications, possibly in different languages, it needs to be implemented in each stack, which is somewhat redundant; and
- 应用范围:同样的协议。如果您有多个应用程序，可能使用不同的语言，那么需要在每个堆栈中实现它，这有点多余;和
- Triggers: transparent to the application and will capture all changes. This is my preferred method.
- 触发器:对应用程序透明，并将捕获所有更改。这是我比较喜欢的方法。
#2
10
Triggers are the quickest and easiest way to achieve simple history. The following information assumes a more complex example where history processing may include some business rules and may require logging information not found in the table being tracked.
触发器是实现简单历史的最快和最简单的方法。下面的信息假设有一个更复杂的示例，其中历史处理可能包含一些业务规则，并且可能需要跟踪表中没有的日志信息。
To those that think that triggers are safer than sprocs because they cannot be bypassed I remind them that they are making the following assumption:
对于那些认为触发因素比sprocs更安全的人，我提醒他们，他们正在做出以下假设:
!) Permissions exist that stop users from executing DISABLE TRIGGER [but then permissions could too exist to limit all access to the database except for EXECUTE on sprocs which is a common pattern for enterprise applications] - therefore one must assume correct permissions and therefore sprocs equal triggers in terms of security and ability to be bypassed
!)权限存在阻止用户执行禁用触发器(但可能也存在限制所有权限访问数据库除了执行存储过程为企业应用程序)是一种常见的模式,因此必须承担正确的权限,因此存储过程平等引发的安全性和能力被忽略
!) Depending on the database it may be possible to execute update statements that do not fire triggers. I could take advantage of knowledge of nested trigger execution depth to bypass a trigger. The only sure solution includes security in database and limiting access to data using only approved mechanisms - whether these be triggers, sprocs or data access layers.
!)根据数据库的不同，可以执行不触发触发器的update语句。我可以利用嵌套触发器执行深度的知识绕过触发器。唯一可靠的解决方案包括数据库中的安全性和仅使用经过批准的机制限制对数据的访问——无论是触发器、sprocs还是数据访问层。
I think the choices are clear here. If the data is being accessed by multiple applications then you want to control the history from the lowest common layer and this will mean the database.
我认为这里的选择很明确。如果数据被多个应用程序访问，那么您希望从最低的公共层控制历史，这将意味着数据库。
Following the above logic, the choice of triggers or stored procedures depends again on whether the stored procedure is the lowest common layer. You should prefer the sproc over the trigger as you can control performance, and side effects better and the code is easier to maintain.
按照上述逻辑，触发器或存储过程的选择再次取决于存储过程是否是最低的公共层。您应该更喜欢sproc而不是触发器，因为您可以更好地控制性能和副作用，并且代码更易于维护。
Triggers are acceptable, but try to make sure that you do not increase locks by reading data outside of the tables being updated. Limit triggers to inserts into the log tables, log only what you need to.
触发器是可以接受的，但是要确保您不会通过读取正在更新的表之外的数据来增加锁。限制触发器插入到日志表中，只记录需要记录的内容。
If the application uses a common logical access layer and it is unlikely that this would change over time I would prefer to implement the logic here. Use a Chain Of Responsibility pattern and a plug-in architecture, drive this from Dependency Injection to allow for all manner of processing in you history module, including logging to completely different types of technology, different databases, a history service or anything else that you could imagine.
如果应用程序使用公共逻辑访问层，而且随着时间的推移，这种情况不太可能改变，我宁愿在这里实现这个逻辑。使用一个职责链模式和一个插件体系结构，从依赖项注入中驱动它，以允许在历史模块中进行所有方式的处理，包括将日志记录到完全不同类型的技术、不同的数据库、历史服务或其他您可以想象的任何东西。
#3
6
Have been using the trigger based approach for years and it has definitely worked well for us, but then you do have the following points to ponder over:
多年来，我们一直在使用基于触发器的方法，它肯定对我们很有效，但是接下来你要考虑以下几点:
-
Triggers on a heavily used (say, a multi-tenant SaaS based application) could be extremely expensive
频繁使用的应用程序(比如基于多租户SaaS的应用程序)的触发器可能非常昂贵
-
In some scenarios, a few fields can get redundant. Triggers are good only when you are crystal clear on the fields to be logged; though using an application you could have an interceptor layer which could help you log certain fields based on the "configuration"; though with it's own share of overheads
在某些场景中，一些字段可能会变得多余。触发器只有当您在要记录的字段上非常清楚时才有效;虽然使用一个应用程序，您可以有一个拦截层，可以帮助您根据“配置”记录某些字段;尽管它也有自己的管理费用
-
Without adequate database control, a person could easily disable the triggers, modify the data and enable the triggers; all without raising any alarms
如果没有足够的数据库控制，一个人可以很容易地禁用触发器、修改数据并启用触发器;所有这些都没有引起任何警报。
-
In case of web applications, where the connections are established from a pool, tracking the actual users who made the changes can be tedious. A possible solution would be to have the "EditedBy" field in every transaction table.
对于从池中建立连接的web应用程序，跟踪做出更改的实际用户可能会很麻烦。一个可能的解决方案是在每个事务表中都有“EditedBy”字段。
#4
5
Late one but it adds couple more options that can be considered.
但它增加了一些可以考虑的选项。
Change Data Capture: This feature is available in SQL Server 2008 R2+ but only in enterprise edition. It allows you to select tables you want to track and SQL Server will do the job for you. It works by reading transaction log and populating history tables with data.
更改数据捕获:此功能在SQL Server 2008 R2+中可用，但仅限于企业版。它允许您选择要跟踪的表，而SQL Server将为您完成任务。它通过读取事务日志和使用数据填充历史表来工作。
Reading transaction log: If database is in full recovery mode then transaction log can be read and details on almost transactions can be found.
读取事务日志:如果数据库处于完全恢复模式，则可以读取事务日志，并找到几乎所有事务的详细信息。
Downside is that this is not supported by default. Options are to read transaction log using undocumented functions like fn_dblog or third party tools such as ApexSQL Log.
缺点是默认情况下不支持。选项是使用未文档化的函数(如fn_dblog)或第三方工具(如ApexSQL log)读取事务日志。
Triggers: Works just fine for small number of tables where there are not too many triggers to manage. If you have a lot of tables you want to audit then you should consider some third party tool for this.
触发器:对于少量没有太多触发器可管理的表来说，这是可以的。如果您有很多表需要审计，那么您应该考虑使用第三方工具进行审计。
All of these work at the database level and are completely transparent to application.
所有这些工作都在数据库级，并且对应用程序完全透明。
#5
2
Triggers are the only reliable way to capture changes. If you do it in Stored Procs or the App, you can always go in and SQL away a change that you don't have a log for (inadvertantly). Of course, somebody who doesn't want to leave a log can disable triggers. But you'd rather force somebody to disable the logging than hope that they remember to include it.
触发器是捕获更改的唯一可靠方法。如果您在存储的Procs或应用程序中执行此操作，您总是可以使用SQL来删除您没有日志记录的更改(无意中)。当然，不想留下日志的人可以禁用触发器。但是，您宁愿强制某人禁用日志记录，也不希望他们记得包含日志记录。
#6
0
Usually if you choose the application layer, you can design your app code to do the logging in a single point, that will handle consistenly all your historical table. differently triggers are a more complicated approach to maintain because they are (depending on the db technology) replicated for every table: in case of hundred of tables the amount of code for the trigger coud be a problem.
通常，如果您选择应用层，您可以设计您的应用程序代码来在一个点上进行日志记录，这将稳定地处理所有历史表。不同的触发器是一种更复杂的维护方法，因为它们(取决于db技术)对每个表都进行了复制:对于数百个表，触发器的代码量是一个问题。
if you have a support organization that will maintain the code you are writing now, and you don't know who will maintain your code (tipical for big industries) you cannot assume which is the skill level of the person who will do fix on your application, in that case it is better in my opinion to make the historical table working principle as simple as possible, and the application layer is probably the best place for this purpose.
如果你有一个支持组织将保持你现在写的代码,和你不知道谁会维护你的代码(典型的大行业)你不能承担的技术水平的人会修复你的应用程序,在这种情况下最好是在我看来,让历史表尽可能的简单工作原理,和应用程序层可能是最好的地方。