历史表利弊,利弊和陷阱 - 使用触发器,sproc或应用程序级别 [英] History tables pros, cons and gotchas - using triggers, sproc or at application level
问题描述
我目前正在玩在我的数据库中有一些表的历史表的想法。基本上我有主表和该表的副本,其中包含修改的日期和操作列,用于存储执行的操作,例如更新,删除和插入。
我可以想到三个不同的地方,你可以做历史表工作。
- 在主表上触发更新,插入和删除。 (数据库)
- 存储过程。 (数据库)
- 应用层。 (应用程序)
我的主要问题是,在每个层中进行工作的利弊, / p>
通过使用触发器方式,我可以想到的一个优点是,无论在数据库上面实施什么程序,都始终保持完整性。
我这样说:
- Stored procs:如果您直接修改表格,它们会被绕过。数据库上的安全性可以控制此
- 应用程序:同一笔交易。此外,如果您有多个应用程序,可能使用不同的语言,它需要在每个堆栈中实现,这有点多余;和
- 触发器:对应用程序透明,并捕获所有更改。这是我首选的方法。
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.
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.
这篇关于历史表利弊,利弊和陷阱 - 使用触发器,sproc或应用程序级别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!