Re: Cascade deletion with ri constraints using triggers

In section "Cascading Triggers" ( see "INFORMIX: Using Triggers", p.2-[29-30] ) a lot of words of wisdom are written. Below is some extractions from the section "The database server allows triggers to cascade, meaning that the triggered actions of a trigger can activate another trigger. The maximum number of triggers in a cascading sequence is 61, the initial trigger plus a maximum of 60 cascading triggers. When the number of cascading triggers in a series exceeds the maximum, the database server returns error number -748: Exceeded limit on maximum number of cascaded triggers. " [...] "Note that when you are using either the INFORMIX-SE database server, or the INFORMIX-OnLine database server without logging, primary key con- straints on both manufact and stock tables would prohibit the triggers in this example. When you use INFORMIX-OnLine with logging, the triggers execute successfully because constraint checking is deferred until the trig- gered actions are complete, including the actions of cascading triggers." [...] " +-----------------------------------------------------------------------+ [SE] |For an INFORMIX-SE database, this or without logging, constraint check-| |ing occurs prior to the triggered action. If a constraint violation | |results from the triggering statement, INFORMIX-SE returns an error | |immediately. | +-----------------------------------------------------------------------+" So, a direct cascading deletion will work for INFORMIX-OnLine database with logging only. BUT !!! Imagine you are a gardener and you want to cut off an old apple tree branch. The direct way is to start to saw this branch nearby its base. The result is very bad for you if you are standing under it. You need to cut off leaves first, then thin branches, etc. But how to get to know whether or not a leaf belongs to big branch you want to delete? The answer is TO COLOUR THE WHOLE BIG BRANCH. How? Because a branch is still alive, put some coloring agent ( eg, red ) nearby its base. After week or so all leaves and small branches get red, you take a stair and scissors and do your job STARTING with RED-COLORED leaves. In case with database: colour agent is a table field you reserve for this purpose. It may be any existing record status field, eg, rec_stat char(2). Colouring process is a cascading UPDATION of the rec_stat field with new value, eg, "DR" (dropped) using triggering mechanism. Final cascading deletion is provided by REVERSED deleting triggering mechanism, which is initiated by the last UPDATE action. Example: You have to delete records from the following simple branch using some search criteria: (tab1) -> (tab2) -> [tab3] First, create two sets of triggers, UPDATE and DELETE ( assuming that each table has field rec_stat char(2)): UPDATE triggers. ---------------- { table tab1 } CREATE TRIGGER upd_tab1 UPDATE OF rec_stat ON tab1 REFERENCING OLD AS pre_upd FOR EACH ROW (UPDATE tab2 SET rec_stat = "DR" WHERE tab2_pk = pre_upd.tab1_fk2); { table tab2 } CREATE TRIGGER upd_tab2 UPDATE OF rec_stat ON tab2 REFERENCING OLD AS pre_upd FOR EACH ROW (UPDATE tab3 SET rec_stat = "DR" WHERE tab3_pk = pre_upd.tab2_fk3); { table tab3 - the last action, initiates reversed deletion } CREATE TRIGGER upd_tab3 UPDATE OF rec_stat ON tab3 AFTER (DELETE FROM tab3 WHERE rec_stat = "DR" ); DELETE triggers. (in reversed order) ------------------------------------ { table tab3 } CREATE TRIGGER del_tab3 DELETE ON tab3 AFTER (DELETE FROM tab2 WHERE rec_stat = "DR" ); { table tab2 } CREATE TRIGGER del_tab2 DELETE ON tab2 AFTER (DELETE FROM tab1 WHERE rec_stat = "DR" ); So, after that the function, deleting from table tab1, looks like as follow: FUNCTION delete_from_tab1() UPDATE tab1 SET rec_stat = "DR" WHERE your criteria END FUNCTION In case of multiple tree, eg: (tab1) -> (tab2) -> [tab3] | +-----> [tab4] you have to use STORED PROCEDURE instead of single SQL statement in tab2 CREATE TRIGGER statement: { table tab2 } CREATE TRIGGER upd_tab2 UPDATE OF rec_stat ON tab2 REFERENCING OLD AS pre_upd FOR EACH ROW (EXECUTE PROCEDURE upd_tab3_and_tab4( pre_upd.tab2_fk3, pre_upd.tab2_fk4 )); Procedure upd_tab3_and_tab4 updates both tab3 and tab4 tables. Well, unfortunately I have no chance to test and debug idea on alive DB, because of my schedule, if you find it interesting and will try to use it, please email me result. Thanks in advance, Take care, Pavel. <pvl@cso.spb.su>