RI Triggers – Gen or DBMS?

Sorry for the delay since the last posting, but we have been very busy at IET with launching a new release along with a couple of new products.

We recently had a discussion with a customer regarding the difference between Gen and DBMS RI enforcement.

The advantage of using DBMS RI is that the RI integrity is maintained by the database and not Gen generated code and hence any program or interactive SQL that deletes records will ensure RI integrity is maintained, whereas with Gen RI, you must either always perform deletes using Gen programs or ensure that your non-Gen programs or SQL correctly maintain RI integrity by cascade deleting child rows, setting foreign keys to NULL, etc.

However one important consideration is that many DBMS products do not support the full range of delete rules that can be defined in Gen. One example is a pendant delete, where the parent row is deleted when the last child is deleted. In this situation, Gen will enforce the rules that cannot be enforced by the DBMS, so that the generated RI triggers contain a mixture of Gen and DBMS enforced rules.

The danger with this situation is that you might think that all RI is enforced by the DBMS and hence not worry about deletes performed outside of Gen, however the DBMS would only be performing some of the deletes and hence the results would differ between using Gen to perform a delete compared with non-Gen programs.

Another consideration with DBMS RI is that you must ensure that the DBMS rules are kept up to date, on all databases, for example, development, test and production.

For these reasons, we use Gen enforced RI for our products.