历史表的优点、缺点和问题 - 使用触发器、sproc 或在应用程序级别
我目前正在考虑在我的数据库中为我的一些表创建历史表的想法.基本上，我有主表和该表的副本，其中包含修改日期和操作列，用于存储执行的操作，例如更新、删除和插入.
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.
相关文章