Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

History tables pros, cons and gotchas - using triggers, sproc or at application level [closed]

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.

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

like image 269
Nathan W Avatar asked Aug 09 '09 03:08

Nathan W


People also ask

What are the advantages and disadvantages of using triggers in a database?

triggers are invoked automatically before or after a change is made to the data in tables. In disadvantage: However, SQL triggers are invoked and executed invisibly from client-applications therefore it is difficult to figure out what happen in the database layer. This may increase the overhead of the database server.

Why we should not use triggers in SQL?

In my experience, they are not a good idea because they can result in surprising side effects, and are difficult to debug (especially when one trigger fires another). Often developers do not even think of looking if there is a trigger.


1 Answers

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.
like image 166
cletus Avatar answered Sep 28 '22 13:09

cletus