I am about to added two columns to a table named PROCESSED_LINE. The columns currently exist on a table named MKT_CNTRY. I realize I could do a JOIN between these two tables, but one of the JOIN columns contains a size mismatch (MKT_CNTRY.PART_NUMBER CHAR(12) and PROCESSED_LINE.SHIP_PROD_ID CHAR(18)).
Anyway, I would like to duplicate the two columns to the PROCESSED_LINE table and maintain them over time with a trigger on the MKT_CNTRY table (the master table).
I should also point out that the MKT_CNTRY table is owned and maintained by a different department at the company where I work. As such, I am concerned with creating a dependency between the two tables.
I am concerned that this might happen:
1. Someone updates or INSERTs a row into the MKT_CNTRY table.
2. The trigger fires and tries to update the PROCESSED_LINE table.
3. The PROCESSED_LINE table is not available for whatever reason.
4. The UPDATE to MKT_CNTRY is rolled back due to the unavailability of the PROCESSED_LINE table.
The department that maintains the MKT_CNTRY table will not be pleased since their system’s availability is now tied to my system.
Is the above “ROLLBACK” scenario only valid if we include the SIGNAL section of the trigger statement? Stated differently, if we excluded the SIGNAL portion of the trigger would the MKT_CNTRY processing continue regardless of the availability of the system which contains the PROCESSED_LINE table?
I read an article about DB2 triggers at http://www.ibm.com/developerworks/data/library/techarticle/0308bhogal/0308bhogal.html
I am working with DB2 for z/OS, but I believe the information is applicable.