MVSFORUMS.com

Nancy · Beginner Joined: 23 May 2003 Posts: 77 Topics: 26

What is DELETE CASCADE rule ? Is it something related to the concept of referential integrity ?

Nancy

bauer · Posted: Tue Jul 19, 2005 5:59 am Post subject:

Nancy,

yes.

DELETE CASCADE rule meas:

If records (rows) in the parent table are delete, the corresponding records in the child table (s) are deleted.

Other possible delete rules are: SET NULL or RESTRICT.

regards,
bauer

blitz2 · Beginner Joined: 23 Jan 2007 Posts: 84 Topics: 14

I've got a peculiar problem with triggers
parent table T1, Child table T2 .. T2 is defined with delete cascade

both tables, T1 and T2, have triggers defined that insert rows in another table T3 with AFTER DELETE.

So when we delete a row from T1, 3 rows from T2 get deleted due to cascading. We were expecting 4 inserted rows in T3. But we are getting a -723 instead. The triggers are giving a problem.

key for T3 is timestamp column. Because of the cascaded deletes, T2 trigger may be trying to insert 3 rows into T3 with the same timestamp. Can this be the problem?

I checked out the following link,
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/2.6.7?DT=20010710165542

but there is no mention of cascades here.
________
buy no2 vaporizer

bauer · Posted: Thu Jan 25, 2007 11:05 am Post subject:

blitz2,

is the timestamp column in t3 primary unique key ?

Does the trigger try to insert into t3 with CURRENT TIMESTAMP ?

Well, and I'm not sure about the situation that t2 is delete by cascade. Does the DB2 trigger work in this situation correct? I'm not sure about that. I only worked with update triggers on tables.

regards,
bauer

blitz2 · Beginner Joined: 23 Jan 2007 Posts: 84 Topics: 14

1) yes, t3 timestamp is primary unique key.

2) yes, trigger inserts into t3 with current timestamp.
________
Kalos

bauer · Posted: Tue Jan 30, 2007 10:26 am Post subject:

Well, my idea is:

Each delete in t1 results by delete rule in several deletes in table t2. this triggers the inserts into t3.

Because it's one statement the current timestamp dosn't change executing this statement. So you have the same timestamp for each insert in t3. Because t3 has primary key for this timestamp column, you get duplicates, result is -723.

This is my explenation based on your comments.

BUT i did NOT check this, with my own sample table and / or reading the manual.

My be you can modify table t3 and add a additional field to the primary key to get it unique.

bauer

dbzTHEdinosauer · Posted: Tue Jan 30, 2007 11:00 am Post subject:

the trigger is invoking a procedure.
what is the procedure doing? is this a MASS INSERT?

the t3 timestamp.
is this acquired by virtue of CURRENT_TIMESTAMP during the INSERT or is the t3 TS "being passed"?

has this ever worked?

You do not need to add an additional col to your t3 key. insure that the UPDATE procedure does not do a mass update, and that the t3 TS is acquired during the update as a SET operand and not as a passed value.

Now, if for some reason you need to have a "particular" TS (passed value), then you are not using TS in T3 properly. should not use as TS intended as a random unique key in a cumulative type table to also be a foreign key.
_________________
Dick Brenholtz
American living in Varel, Germany

blitz2 · Beginner Joined: 23 Jan 2007 Posts: 84 Topics: 14

dbz, t3 timestamp is CURRENT TIMESTAMP.

Bauer, thanks for the response. Looks like that is indeed the problem.
________
oxygen vaporizer