Problem solve Get help with specific problems with your technologies, process and projects.

Generating images of DB tables

Is there any short side to using triggers for generating before and after images of DB tables? If so, what are they?
Well, modifications made to a table with triggers on it will perform worse than to one without triggers on it. But of course, that is an incomplete answer. You need to judge the entire process - that is the modification plus the "capture of the before/after image" you need to do. Triggers should be well suited for your purposes if you wish to record the data the way it was before the modification and then the way it is after.

However, you might want to re-consider why you want to do that? All of that data already is captured by DB2 in the logs. With a log analysis product (such as Log Master for DB2).

You may have perfectly valid reasons for wishing to do this using triggers. Other things to keep in mind when using triggers are the management and development issues.

Management issues include building/altering triggers appropriately, ensuring they fire in the proper order (probably not a big concern for you though) avoiding more than 15 levels of trigger nesting and using triggers with LOAD. LOAD will not fire INSERT triggers so be aware of that.

The biggest development issue is debugging. It can be difficult to ensure that the triggers are working properly as you create them. I've seen some shops use "trigger columns" in their tables to help debug problems. These columns are simply a TRIGGER_NAME and TRIGGER_TIMESTAMP that records the name of the trigger that last accessed the trigger row and the timestamp when it ran. You may or may not want to include these columns depending on the logic that you will be putting in your triggers.

Good luck.

Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our .VO7aaqqaAFk.0@/search390>discussion forums.

Dig Deeper on IBM DB2 management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.