This blog post has moved. Please find it at:
http://www.mpopp.net/2006/01/creating-log-tables-with-triggers/.
Subscribe to:
Post Comments (Atom)
skip to main |
skip to sidebar
This is all about db4free.net and the database systems that it offers. The main focus are practical examples of the new features of MySQL 5 and 6 that should show you how to use them for your advantage in your daily life ;-).
About Me
Links
Blog Archive
-
▼
2006
(124)
-
▼
January
(25)
- No MySQL forum category for migration from Postgre...
- Book by Karl Fogel - Producing Open Source Software
- How to deal with repeating date intervals?
- Joomla still doesn't work with SQL mode enabled
- The 'to enum or not to enum' question
- Emulating check constraints with views?
- A little look into the (near) future
- Emulating check constraints
- Is this a bug?
- Creating log tables with triggers
- XAMPP
- Ideas for db4free.net?
- Calculating time spans and ... a bug?
- Everybody can post comments again ;-) ...
- MySQL 5.0 Beta Certification Exams available
- db4free.net CleanUp process (2)
- mysqldump and mysqlcheck now work perfectly
- What's wrong with MySQL QueryBrowser?
- MySQL 5.1 evolves
- mysqldump's tz-utc trap
- A little join problem
- Index information from information_schema
- PostgreSQL's MySQL compatibility functions
- Hail to GROUP_CONCAT!
- Happy and successful new year!
-
▼
January
(25)
10 comments:
In theory - yes, but as the log table grows, it would be quite slow to reconstruct the data table.
The log table should serve rather to keep track on what happened to the data table instead of actually working with the data. So if e.g.somebody deletes a record in the data table, the log table would help to find out, who did the deletion at which time (it would of course also be possible to log, which user did the modification).
In some cases it's useful to have duplicate information (even though data should be normalized, there are situation where it makes sense to denormalize it - e.g. to abstract parts of the data to make it easier accessible). In this particular case, the data isn't even fully denormalized, because both tables show different entities of the data, so it's not the same piece of information. There are numerous situations where a log table which is set up something like this can make a lot of sense.
Markus, thanks a lot for your post. I am sure it will help many DBAs myself included.
Thanks again,
Frank Mash
I really like the cool names you used to populate the table :)
It's always nice to hear that the articles really help somebody :-).
Thanks for the info you've provided here! I've been mulling over how I was going to log transactions on my application without changing all my code, and this has saved me a butt-load of time!
Could you log schema modifications, for alter tables, drop o create?
useful to know who created indexes, tables o altered them...
Very interesting question. I'm not absolutely sure whether it's possible to create triggers on the information_schema views. If this works, it should be possible, otherwise I wouldn't know a way how to accomplish this.
But it's certainly an interesting thing to experiment with ;-).
I played around with it, but didn't manage to create a trigger on an information_schema system view. I'm afraid that this is currently not possible, but maybe somebody else has an ingenious idea how to do this ;-).
This is excellent! Exactly what I was looking for. Thanks for your efforts.
Im sorry for my english, im from Mexico..
I know this post its a little old, but i have a doubt, question about this...
How would you do this when a select is used using joins?
How would you do the trigger?
Not sure if I understand correctly what you mean, but SELECT statements wouldn't affect the log tables. Only queries that change data (INSERT/UPDATE/DELETE) leave a record in the log tables, there are no triggers for SELECTs though.
Post a Comment