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.
Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our
Dig Deeper on IBM DB2 management
Our expert suggests that the best way to pass the SAP BW exam is to have hands-on job experience. But that's not all he has to say about ...
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ...
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2.