In our second example trigger program, we take a closer look at the shortcomings of the delete cascade referential constraint, and how we might use a trigger program to overcome those shortcomings. Here is the problem: A delete trigger cannot be added to a dependent file if that file is under a delete cascade referential constraint.
For example, consider the database of a typical bank. It might contain an account header file (call it ACCTS) and an account transaction detail file (call it ATRANS). The ACCTS file stores the general account information (e.g., account number, customer number, balance, etc.). While the ATRANS file has a record for each transaction, storing information about the transaction (e.g., account number, date, transaction type, amount, etc.). The MIS department would like to implement a delete cascade constraint so that, when a customer closes their account and the associated ACCTS record is deleted, all the corresponding ATRANS records are also deleted. The MIS department also wants to have a delete trigger program attached to the ATRANS file that will copy deleted records to an archive file, which later gets copied to microfiche or other permanent storage media. However, this is not allowed.
The Solution: Use a trigger program to simulate the delete cascade constraint
Instead of defining a delete cascade referential constraint on the ACCTS file, attach a trigger program to the file that will simulate the delete cascade constraint. You will then be able to use a delete trigger on the "dependent" ATRANS file.
The DLTATRANS.RPG program is added to ACCTS file as a delete trigger. When a record is deleted from the ACCTS file, the system calls this trigger program and it deletes the associated records from the ATRANS file. As each ATRANS record is deleted, a delete trigger (not shown since it is not the point of this example) defined on the ATRANS file is called and writes a copy of the record being deleted to the archive file. Put the DLTATRANS trigger program to work using the following command:
ADDPFTRG FILE(library/ACCTS) TRGTIME(*BEFORE) TRGEVENT(*DELETE) PGM(library/DLTATRANS) TRG(DLTATRANS)
Notice that the DLTATRANS trigger program opens the ATRANS file under commitment control. The application deleting the record from the ACCTS file must similarly open the ACCTS file under commitment control. If an error occurs during the execution of the trigger (e.g., an ATRANS record is locked), it sends escape message MSG0001 (which you need to define in a message file) to the application program, which should monitor for the error and execute a ROLBK operation to rollback the deleted ACCTS record, as well as any records in the ATRANS file that may have been successfully deleted by the trigger before the error occurred. When the trigger completes normally, the application program should execute a COMIT operation.
Additionally, you should use the following command to add delete restrict and update restrict referential constraints to the ATRANS file:
ADDPFCST FILE (library/ATRANS) TYPE(*REFCST) KEY(ACCTNO) PRNFILE(library/ACCTS) PRNKEY(ACCTNO) DLTRULE(*RESTRICT) UPDRULE(*RESTRICT)
This implicitly adds an insert restrict constraint to the file as well, which guards against inserting a record in ATRANS that does not have an associated record in ACCTS (if it could have been used, a delete cascade constraint would have done this as well).
The benefit of this approach
Because a trigger is used to implement the delete cascade referential constraint, a delete trigger can be added to the dependent file. And, because the DLTATRANS trigger runs under the same commitment control definition as the application program and because the dependent file is guarded with an insert restrict referential constraint, the design is as sound as using the delete cascade referential constraint.
-----------------------------------
About the author: Ron Turull is editor of Inside Version 5. He has more than 20 years' experience programming for and managing AS/400-iSeries systems.
