历史表的优点、缺点和问题 - 使用触发器、sproc 或在应用程序级别 [英] History tables pros, cons and gotchas - using triggers, sproc or at application level

问题描述

我目前正在考虑在我的数据库中为我的一些表创建历史表的想法.基本上，我有主表和该表的副本，其中包含修改日期和操作列，用于存储执行的操作，例如更新、删除和插入.

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 e.g., Update, Delete and Insert.

到目前为止，我可以想到三个不同的地方可以进行历史表工作.

So far I can think of three different places that you can do the history table work.

• 在主表上触发更新、插入和删除.(数据库)
• 存储过程.(数据库)
• 应用层.(申请)

我的主要问题是；在每一层中进行工作的优缺点是什么?

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 is implemented on top of the database.

推荐答案

我会这样说:

• 存储过程:如果您直接修改表，它们将被绕过.数据库的安全性可以控制这一点
• 申请:同样的交易.另外如果你有多个应用程序，可能是不同的语言，则需要在每个堆栈中实现，这有点多余；和
• 触发器:对应用程序透明，并将捕获所有更改.这是我的首选方法.

• 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.

这篇关于历史表的优点、缺点和问题 - 使用触发器、sproc 或在应用程序级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！