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.
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:
!) 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.
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.
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.
对于从池建立连接的 Web 应用程序来说，跟踪进行更改的实际用户可能会很乏味。 一个可能的解决方案是在每个事务表中都包含“EditedBy”字段。
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
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.
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.
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.
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.
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.
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.
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.
发布评论
评论（6）
我这样说：
I'd put it this way:
触发器是实现简单历史记录的最快、最简单的方法。 以下信息假设了一个更复杂的示例，其中历史处理可能包括一些业务规则，并且可能需要在正在跟踪的表中找不到的日志记录信息。
对于那些认为触发器比存储过程更安全的人，因为它们无法被绕过，我提醒他们，他们正在做出以下假设：
！）存在阻止用户执行 DISABLE TRIGGER 的权限 [但是权限也可能存在以限制对存储过程中的 EXECUTE 除外，这是企业应用程序的一种常见模式] - 因此必须假设正确的权限，因此存储过程在安全性和被绕过的能力方面具有相同的触发器
！）根据数据库，可能会执行更新语句不触发触发器。 我可以利用嵌套触发器执行深度的知识来绕过触发器。 唯一可靠的解决方案包括数据库安全性以及仅使用批准的机制限制对数据的访问 - 无论这些机制是触发器、存储过程还是数据访问层。
我认为这里的选择很明确。 如果数据被多个应用程序访问，那么您希望从最低公共层控制历史记录，这将意味着数据库。
遵循上述逻辑，触发器或存储过程的选择再次取决于存储过程是否是最低公共层。 您应该更喜欢存储过程而不是触发器，因为您可以更好地控制性能和副作用，并且代码更易于维护。
触发器是可以接受的，但请尝试确保不会通过读取正在更新的表之外的数据来增加锁定。 将触发器限制为插入日志表，仅记录您需要的内容。
如果应用程序使用公共逻辑访问层并且这不太可能随着时间的推移而改变，我更愿意在此处实现逻辑。 使用责任链模式和插件架构，从依赖注入驱动它，以允许在历史模块中进行各种处理方式，包括记录到完全不同类型的技术、不同的数据库、历史服务或您需要的任何其他内容。可以想象。
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:
!) 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.
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.
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.
多年来一直使用基于触发器的方法，它对我们来说确实效果很好，但是您确实需要考虑以下几点：
频繁使用的触发器（例如，基于多租户 SaaS 的应用程序）可以非常昂贵
在某些情况下，一些字段可能会变得多余。 只有当您非常清楚要记录的字段时，触发器才是有用的； 尽管使用应用程序，您可以拥有一个拦截器层，它可以帮助您根据“配置”记录某些字段； 尽管有自己的开销份额
如果没有足够的数据库控制，人们可以轻松地禁用触发器、修改数据并启用触发器； 一切都不会引发任何警报
对于从池建立连接的 Web 应用程序来说，跟踪进行更改的实际用户可能会很乏味。 一个可能的解决方案是在每个事务表中都包含“EditedBy”字段。
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
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.
迟到了，但它增加了更多可以考虑的选项。
更改数据捕获： 此功能在 SQL Server 2008 R2+ 中可用，但仅在企业版中可用。 它允许您选择要跟踪的表，SQL Server 将为您完成这项工作。 它的工作原理是读取事务日志并用数据填充历史表。
读取事务日志：如果数据库处于完全恢复模式，则可以读取事务日志，并且可以找到几乎所有事务的详细信息。
缺点是默认情况下不支持。 选项是使用未记录的函数（例如 fn_dblog）或第三方工具（例如 ApexSQL Log）读取事务日志。
触发器：对于没有太多触发器需要管理的少量表来说效果很好。 如果您有很多要审核的表，那么您应该考虑使用一些第三方工具。
所有这些都在数据库级别工作，并且对应用程序完全透明。
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.
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.
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.
触发器是捕获变化的唯一可靠方法。 如果您在存储过程或应用程序中执行此操作，则您始终可以进入并通过 SQL 删除您没有日志的更改（无意中）。 当然，不想留下日志的人可以禁用触发器。 但您宁愿强迫某人禁用日志记录，也不愿希望他们记得包含它。
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.
通常，如果您选择应用程序层，您可以设计应用程序代码来在单点进行日志记录，这将一致地处理所有历史表。 不同的是，触发器是一种更复杂的维护方法，因为它们（取决于数据库技术）会为每个表进行复制：如果有数百个表，触发器的代码量可能会成为问题。
如果您有一个支持组织来维护您现在正在编写的代码，并且您不知道谁将维护您的代码（对于大行业来说很典型），您无法假设修复您的代码的人员的技能水平是多少应用程序，在这种情况下，我认为最好使历史表工作原理尽可能简单，而应用程序层可能是实现此目的的最佳位置。
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.
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.