Must-knows when using referential constraints and triggers together


Ron Turull
Before you add triggers and referential constraints to the same file, there are a few caveats of which you should be aware. The two can be used on the same file. They may complement each other, or they may perform mutually exclusive tasks. Regardless of the design, you should keep the following points in mind.

More Information

1. There is a distinct order in which the system calls triggers and evaluates referential constraints. The order is (from first to last):

  • Before triggers


  • Restrict referential constraints


  • After triggers


  • No-action, cascade, set-default , and set-null constraints


    • That is extremely important if a trigger works in conjunction with a referential constraint. For example, you design a trigger to insert a new parent file record, if needed, when a new dependent file record is inserted. Because there is an implied insert restrict constraint on the files (added the first time you add any constraint), the trigger program must be a before insert trigger so it will be called before the insert restrict constraint is checked.

    2. You cannot add a delete trigger to a file that is a dependent file in a delete cascade referential constraint relationship.

    IBM's reasoning behind that is to avoid a situation where thousands of dependent records are being deleted as a result of the delete cascade constraint and, for each one, delete triggers must be called. The system could come to a grinding halt if it took place in a high-priority job (i.e., interactive job). Unfortunately, there may be times when you want both functions and are willing to accept the consequences. One can only hope that IBM will remove that limitation in a future release.

    Circumvention:

    In the meantime, to get around that restriction you can put the delete triggers on the parent file if the design will support it. Otherwise, you can implement the delete cascade constraint using another trigger (we'll discuss how to do that and see an example in the next few installments of this column).

    3. You cannot add an update trigger to a file that is a dependent file in a delete set-null or a delete set-default referential constraint relationship.

    The reasoning behind that is similar to the previous item (i.e., delete set-null and delete set-default constraints have the potential of updating many records in a dependent file). Again, a possible work-around is to implement the constraint using a trigger.

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


    This was first published in March 2006

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top