Trigger and RI DELETE CASCADE

Michael Arlebrandt

Trigger and RI DELETE CASCADE
Hi,

I got a problem from one of our developers,

They have to tables with RI, FILE and FILE_INSTANCE. FILE is PARENT to FILE_INSTANCE and the RI is defined wth ON DELETE CASCADE

ALTER TABLE T0T250T.FILE_INSTANCE
FOREIGN KEY FKÅFÅFÅN (FK_F_F_NAME
,FK_FFM_FFM_NUMBER)
REFERENCES T0T250T.FILE
ON DELETE CASCADE ;

They have some triggers defined on FILE_INSTANCE that runs som stored procedures to update the FILE table. One trigger/SP updates a column, FILE_INST_EXIST in FILE, with value 'Y' when an row is inserted, and one trigger/SP updates that value to 'N' when the last row in the child table for the corresponding parent is deleted.

And this has been working fine until they started to DELETE rows in the parent table FILE that had corresponding rows in the FILE_INSTANCE table.

Due to the rule ON DELETE CASCADE DB2 also deletes the row in the child table FILE_INSTANCE which fires the Triggers/SP to try to update the row in the parent table which is about to be deleted amd of cource it fails.

So my developer wanted me to suggest a solution for this scenario.

Do you have some ideas of what could be done?

best regards
Michael

_________________________________
Michael Ärlebrandt
Volvo Information Technology
Gothenburg, Sweden
Mailto:[login to unmask email]
_________________________________



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chi-Yun (Angela) Fung

Re: Trigger and RI DELETE CASCADE
(in response to Walter Janißen)
Michael,

I would suggest the developers to change the DELETE rule on FILE_INSTANCE to ON DELETE RESTRICT to force them to delete the child rows first wich will then fire the trigger/sp to update the parent table column file_inst_exist to 'N'. After that they can delete the parent rows as they wish. The only time parent rows can be deleted should be when the indicator is 'N'.

Good luck!

Angela

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Ärlebrandt Michael
Sent: Monday, December 15, 2003 5:58 AM
To: [login to unmask email]
Subject: Trigger and RI DELETE CASCADE



Hi,

I got a problem from one of our developers,

They have to tables with RI, FILE and FILE_INSTANCE. FILE is PARENT to FILE_INSTANCE and the RI is defined wth ON DELETE CASCADE

ALTER TABLE T0T250T.FILE_INSTANCE
FOREIGN KEY FKÅFÅFÅN (FK_F_F_NAME
,FK_FFM_FFM_NUMBER)
REFERENCES T0T250T.FILE
ON DELETE CASCADE ;

They have some triggers defined on FILE_INSTANCE that runs som stored procedures to update the FILE table. One trigger/SP updates a column, FILE_INST_EXIST in FILE, with value 'Y' when an row is inserted, and one trigger/SP updates that value to 'N' when the last row in the child table for the corresponding parent is deleted.

And this has been working fine until they started to DELETE rows in the parent table FILE that had corresponding rows in the FILE_INSTANCE table.

Due to the rule ON DELETE CASCADE DB2 also deletes the row in the child table FILE_INSTANCE which fires the Triggers/SP to try to update the row in the parent table which is about to be deleted amd of cource it fails.

So my developer wanted me to suggest a solution for this scenario.

Do you have some ideas of what could be done?

best regards
Michael

_________________________________
Michael Ärlebrandt
Volvo Information Technology
Gothenburg, Sweden
Mailto:[login to unmask email]
_________________________________


--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: Trigger and RI DELETE CASCADE
(in response to Michael Arlebrandt)
Michael

How does the update of the trigger fail? Does it get a negative SQLCODE or
just a +100, because no row satisfies the WHERE-clause?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Arlebrandt

Re: Trigger and RI DELETE CASCADE
(in response to Chi-Yun (Angela) Fung)
Thanks Angela and Walter for your replies,

Your suggestion Angela was also one of the first proposal I gave them but they hoped that I could give them an alternativ
how to write the SP to be able to handle this instead.

for the SQLCODE we get -723 the trigger and -751 from the SP

-751 object-type object-name (SPECIFIC NAME specific name) ATTEMPTED TO
EXECUTE AN SQL STATEMENT statement THAT IS NOT ALLOWED

/Michael



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of [login to unmask email]
Sent: Monday, December 15, 2003 2:43 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Trigger and RI DELETE CASCADE


Michael

How does the update of the trigger fail? Does it get a negative SQLCODE or
just a +100, because no row satisfies the WHERE-clause?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: Trigger and RI DELETE CASCADE
(in response to Michael Arlebrandt)
Michael

The explanation for -751:

Stored procedure
A stored procedure issued an SQL statement that forced the DB2 thread
to roll back the unit of work. The SQL statement that caused the
thread to be placed in the MUST_ROLLBACK state is one of the
following:

COMMIT
ROLLBACK

All further SQL statements are rejected until the SQL application
that issued the SQL CALL statement rolls back the unit of work. When
control returns to the SQL application that issued the SQL CALL
statement, the SQL application must roll back the unit of work. This
can be done by issuing an SQL ROLLBACK statement or the equivalent
IMS or CICS operation.

Does the stored procedure issue an explicit rollback?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Arlebrandt

Re: Trigger and RI DELETE CASCADE
(in response to Walter Janißen)
I managed to find their sorce for the Cobol SP, they are doing a ROLLBACK

IF SQLCODE NOT = 0

PERFORM S01-SQL-ROLLBACK

S01-SQL-ROLLBACK SECTION.
***************************

EXEC SQL
ROLLBACK
END-EXEC.

So I presume we have a different SQLCODE before the ROLLBACK, I ask them to reproduce the error and I will have a look into
Detector to see what is the original SQLCODE

/Michael


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of [login to unmask email]
Sent: Monday, December 15, 2003 3:36 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Trigger and RI DELETE CASCADE


Michael

The explanation for -751:

Stored procedure
A stored procedure issued an SQL statement that forced the DB2 thread
to roll back the unit of work. The SQL statement that caused the
thread to be placed in the MUST_ROLLBACK state is one of the
following:

COMMIT
ROLLBACK

All further SQL statements are rejected until the SQL application
that issued the SQL CALL statement rolls back the unit of work. When
control returns to the SQL application that issued the SQL CALL
statement, the SQL application must roll back the unit of work. This
can be done by issuing an SQL ROLLBACK statement or the equivalent
IMS or CICS operation.

Does the stored procedure issue an explicit rollback?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

teldb2kals

Re: Trigger and RI DELETE CASCADE
(in response to Michael Arlebrandt)
Hi Michael,

I think you would get an sqlcode of +100 for your update statement, which
should be acceptable. Maybe the code is just checking for sqlcode 0 and
rollback if any other. Is your developer checking for +100 in the code ?

You could also code a WHERE EXISTS clause in your update-parent-table
statement if needed.

Regards,
Kals.

On Mon, 15 Dec 2003 11:58:11 +0100, =?iso-8859-1?Q?=C4rlebrandt_Michael?=
<[login to unmask email]> wrote:

>Hi,
>
>I got a problem from one of our developers,
>
>They have to tables with RI, FILE and FILE_INSTANCE. FILE is PARENT to
FILE_INSTANCE and the RI is defined wth ON DELETE CASCADE
>
>ALTER TABLE T0T250T.FILE_INSTANCE
> FOREIGN KEY FKÅFÅFÅN (FK_F_F_NAME
> ,FK_FFM_FFM_NUMBER)
> REFERENCES T0T250T.FILE
> ON DELETE CASCADE ;
>
>They have some triggers defined on FILE_INSTANCE that runs som stored
procedures to update the FILE table. One trigger/SP updates a column,
FILE_INST_EXIST in FILE, with value 'Y' when an row is inserted, and one
trigger/SP updates that value to 'N' when the last row in the child table
for the corresponding parent is deleted.
>
>And this has been working fine until they started to DELETE rows in the
parent table FILE that had corresponding rows in the FILE_INSTANCE table.
>
>Due to the rule ON DELETE CASCADE DB2 also deletes the row in the child
table FILE_INSTANCE which fires the Triggers/SP to try to update the row in
the parent table which is about to be deleted amd of cource it fails.
>
>So my developer wanted me to suggest a solution for this scenario.
>
>Do you have some ideas of what could be done?
>
>best regards
>Michael
>
>_________________________________
>Michael Ärlebrandt
>Volvo Information Technology
>Gothenburg, Sweden
>Mailto:[login to unmask email]
>_________________________________
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Arlebrandt

Re: Trigger and RI DELETE CASCADE
(in response to teldb2kals)
Thanks all,

I run a trace and found that they get a +100 in their SP and does not check for that. After change in the code it now works fine.

But the strange thing is that I told them the first time they called me som weeks ago to check if the row exists before they
try to delete it ...

anyhow thanks a lot for your assistance.

Michael

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Teldb2kals
Sent: Monday, December 15, 2003 11:16 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Trigger and RI DELETE CASCADE


Hi Michael,

I think you would get an sqlcode of +100 for your update statement, which
should be acceptable. Maybe the code is just checking for sqlcode 0 and
rollback if any other. Is your developer checking for +100 in the code ?

You could also code a WHERE EXISTS clause in your update-parent-table
statement if needed.

Regards,
Kals.

On Mon, 15 Dec 2003 11:58:11 +0100, =?iso-8859-1?Q?=C4rlebrandt_Michael?=
<[login to unmask email]> wrote:

>Hi,
>
>I got a problem from one of our developers,
>
>They have to tables with RI, FILE and FILE_INSTANCE. FILE is PARENT to
FILE_INSTANCE and the RI is defined wth ON DELETE CASCADE
>
>ALTER TABLE T0T250T.FILE_INSTANCE
> FOREIGN KEY FKÅFÅFÅN (FK_F_F_NAME
> ,FK_FFM_FFM_NUMBER)
> REFERENCES T0T250T.FILE
> ON DELETE CASCADE ;
>
>They have some triggers defined on FILE_INSTANCE that runs som stored
procedures to update the FILE table. One trigger/SP updates a column,
FILE_INST_EXIST in FILE, with value 'Y' when an row is inserted, and one
trigger/SP updates that value to 'N' when the last row in the child table
for the corresponding parent is deleted.
>
>And this has been working fine until they started to DELETE rows in the
parent table FILE that had corresponding rows in the FILE_INSTANCE table.
>
>Due to the rule ON DELETE CASCADE DB2 also deletes the row in the child
table FILE_INSTANCE which fires the Triggers/SP to try to update the row in
the parent table which is about to be deleted amd of cource it fails.
>
>So my developer wanted me to suggest a solution for this scenario.
>
>Do you have some ideas of what could be done?
>
>best regards
>Michael
>
>_________________________________
>Michael Ärlebrandt
>Volvo Information Technology
>Gothenburg, Sweden
>Mailto:[login to unmask email]
>_________________________________
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm