Steps to Identify Action Blocks that use Referential Integrity (RI) Triggers

Document ID : KB000027991
Last Modified Date : 14/02/2018
Show Technical Document Details

Summary:

Steps to Identify Action Blocks that use Referential Integrity (RI) Triggers.

Instructions:

When doing analysis on a data model or considering changes to a data model that will affect Referential Integrity (RI) Triggers, it is useful to identify all of the action blocks that use RI triggers. Any change to a relationship or to the identifier for an entity type has the potential to require regeneration of RI triggers and the action diagrams that use them.

The SQL below looks at all DELETE, DISASSOCIATE, and TRANSFER statements in a model to find action blocks that contain those statements since those are the statements that use RI triggers. This article lists the SQL, for the Host Encyclopedia and the CSE, and then describes the differences between the two.

For reference, the metamodel codes used are

Object type codes
21      ACBLKBSD
23      ACBLKBAA
27      DELETE
39      PRDRD (disassociate)
40      PRDRT (transfer)

Association type codes
3        ACTDON
6        ACTSON
25      CNTENTS (entity action view group)
26      CNTIMPS (import view group)
31      CNTOUTS (export view group)
36      CONTAINS
76      DTLBYP
88      GRPBY

Property type code
224      NAME

Host Encyclopedia SQL

select distinct p.prop_char_val, h.obj_id, ot_obj_name  from dmdl a, dobj h, dasc g, dasc f, dasc d, dprp p,       dasc c, dobj b, sobj where a.model_name = ' your model '   and h.obj_model_id = a.model_id   and h.obj_type_code = 27   and ot_obj_code = h.obj_type_code   and ot_release = 'release'   and h.obj_id = g.assoc_to_obj_id   and g.assoc_type_code = 3    and g.assoc_from_obj_id = f.assoc_to_obj_id   and f.assoc_type_code = 36    and d.assoc_to_obj_id = f.assoc_from_obj_id   and d.assoc_type_code in (25, 31, 26)    and c.assoc_to_obj_id = d.assoc_from_obj_id   and c.assoc_type_code = 88   and c.assoc_from_obj_id = b.obj_id   and b.obj_type_code in (21, 23)    and p.prop_obj_id = b.obj_id   and p.prop_type_code = 224   union select distinct p2.prop_char_val, h2.obj_id, ot_obj_name   from dmdl a2, dobj b2, dasc c2, dasc d2, dasc f2,        dobj h2, dasc j2, dasc i2, dprp p2, sobj  where a2.model_name = ' your model '    and h2.obj_model_id = a2.model_id    and h2.obj_type_code in (39, 40)     and ot_obj_code = h2.obj_type_code    and ot_release = 'release'    and i2.assoc_from_obj_id = h2.obj_id    and i2.assoc_type_code = 6     and j2.assoc_to_obj_id = i2.assoc_to_obj_id    and j2.assoc_type_code = 76     and j2.assoc_from_obj_id = f2.assoc_to_obj_id    and f2.assoc_type_code = 36     and d2.assoc_to_obj_id = f2.assoc_from_obj_id    and d2.assoc_type_code in (26, 31, 25)     and c2.assoc_to_obj_id = d2.assoc_from_obj_id    and c2.assoc_type_code = 88     and c2.assoc_from_obj_id = b2.obj_id    and b2.obj_type_code in (21, 23)     and p2.prop_obj_id = b2.obj_id    and p2.prop_type_code = 224

Client / Server Encyclopedia SQL

select distinct b.obj_name, h.obj_id, ot_obj_name  from dmdl a, dobj h, dasc g, dasc f, dasc d,       dasc c, dobj b, sobj where a.model_name = ' your model '   and h.obj_model_id = a.model_id   and h.obj_type_code = 27    and ot_obj_code = h.obj_type_code   and ot_release = 'release'   and h.obj_id = g.assoc_to_obj_id   and g.assoc_type_code = 3    and g.assoc_from_obj_id = f.assoc_to_obj_id   and f.assoc_type_code = 36    and d.assoc_to_obj_id = f.assoc_from_obj_id   and d.assoc_type_code in (25, 31, 26)    and c.assoc_to_obj_id = d.assoc_from_obj_id   and c.assoc_type_code = 88    and c.assoc_from_obj_id = b.obj_id   and b.obj_type_code in (21, 23)   union select distinct b2.obj_name, h2.obj_id, ot_obj_name   from dmdl a2, dobj b2, dasc c2, dasc d2, dasc f2,        dobj h2, dasc j2, dasc i2, sobj  where a2.model_name = ' your model '    and h2.obj_model_id = a2.model_id    and h2.obj_type_code in (39, 40)     and ot_obj_code = h2.obj_type_code    and ot_release = 'release'    and i2.assoc_from_obj_id = h2.obj_id    and i2.assoc_type_code = 6     and j2.assoc_to_obj_id = i2.assoc_to_obj_id    and j2.assoc_type_code = 76     and j2.assoc_from_obj_id = f2.assoc_to_obj_id    and f2.assoc_type_code = 36     and d2.assoc_to_obj_id = f2.assoc_from_obj_id    and d2.assoc_type_code in (26, 31, 25)     and c2.assoc_to_obj_id = d2.assoc_from_obj_id    and c2.assoc_type_code = 88     and c2.assoc_from_obj_id = b2.obj_id    and b2.obj_type_code in (21, 23)

The difference between the two sets of SQL reflects a difference in the way properties are stored on the different encyclopedia types. The NAME property is stored in the DOBJ table in the CSE; on the Host Encyclopedia it is stored in the DPRP table.

In both sets of SQL, the action block name and id and the statement type (DELETE, PRDRD, or PRDRT) are displayed in the output. Don't forget when using this SQL to replace all references to 'your model' with your model name and references to 'release' to the relevant release for the model (i.e. 9.2.A6 for CA Gen 8.x models, or the corresponding schema release for future CA Gen versions).