trigger for UPDATE CASCADE

Buz W

trigger for UPDATE CASCADE
I know we can't be the only ones wanting to do this...

We want to UPDATE a parent table row's primary key and propagate the key changes to all the rows of dependent tables. A TRIGGER seems to be the logical answer, but:

1) AFTER triggers won't work because we can't modify the parent because there are dependent rows and
2) BEFORE triggers won't work because BEFORE triggers can't UPDATE.

Are we stuck putting this in application code?

Any ideas when DB2 for OS/390 is going to support ON UPDATE CASCADE?

Thanks.

Buz Williams
Sophisticated Business Systems Inc.
972.664.9005; 800.801.9005


Peter Backlund

Re: trigger for UPDATE CASCADE
(in response to Buz W)
Buz,

This can easily be solved using a combination of before and after triggers.
It works perfectly in DB2 for Linux, UNIX, Windows

If you need the solution, you can contact me directly.

It doesn't currently work in DB2 for z/OS,
the problem is described in APAR PQ60664

Best regards,

Peter

Buz W wrote:

> I know we can't be the only ones wanting to do this...
>
> We want to UPDATE a parent table row's primary key and propagate the
> key changes to all the rows of dependent tables. A TRIGGER seems to be
> the logical answer, but:
>
> 1) AFTER triggers won't work because we can't modify the parent
> because there are dependent rows and
> 2) BEFORE triggers won't work because BEFORE triggers can't UPDATE.
>
> Are we stuck putting this in application code?
>
> Any ideas when DB2 for OS/390 is going to support ON UPDATE CASCADE?
>
> Thanks.
>
>
> Buz Williams
> Sophisticated Business Systems Inc.
> 972.664.9005; 800.801.9005
>

--

=====> See you in October, 2003 at IDUG in Nice, France <======

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+