Wednesday, January 25, 2006

Creating log tables with triggers

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/01/creating-log-tables-with-triggers/.

11 comments:

Scott Noyes said...

Leads me to wonder - why have the data table at all? You could use just the log table, and create a view that would show the contents of your current data table.

I assume that would be terribly inefficient when it comes to indexing or something, but it sure avoids duplicating information.

Markus Popp said...

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.

Frankly Speaking! said...

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 :)

Markus Popp said...

It's always nice to hear that the articles really help somebody :-).

Mike said...

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!

ingo said...

Could you log schema modifications, for alter tables, drop o create?
useful to know who created indexes, tables o altered them...

Markus Popp said...

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 ;-).

Markus Popp said...

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 ;-).

StvOgdn said...

This is excellent! Exactly what I was looking for. Thanks for your efforts.

Anonymous said...

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?

Markus Popp said...

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.