restrict record deletions between tables.

[shorti]

Here is an example of what I want to do (syntax might not be entirely
correct as this is just an example):

CREATE TABLE ParentA
(
name CHAR (6) NOT NULL;
address CHAR(64);
) IN CUSTOMER_TS INDEX IN CUSTOMER_TS;

CREATE TABLE Child1
(
name CHAR(6) NOT NULL;
otherinfo CHAR(64);
) IN CUSTOMER_TS INDEX IN CUSTOMER_TS:

ALTER TABLE ParentA PRIMARY KEY (name);
ALTER TABLE Child1 FOREIGN KEY CUSTNAME (name) REFERENCES ParentA ON
DELETE CASCASE;
CREATE TRIGGER name_insert AFTER INSERT ON ParentA REFERENCING NEW AS
new_temp FOR EACH ROW INSERT INTO Child1(name) VALUES (new_temp.name) ;

So basically, when a record is inserted into the ParentA table, a
Child1 records is also created. Also, If the ParentA record is
deleted, the Child1 record is also deleted.

What I also want to do is to restrict someone from deleting the record
in Child1 if the record in Parent1 exists (which it should always
exists). I just dont want them to be able to delete the child...if the
record is to be remove, then its both or nothing. I am also worried
about getting caught up behind the delete cascade too if I am able to
restrict the child from being deleted.

Any suggestions?

[Shyam Peri]

shorti,
The following trigger over the child(s) might help u out !

=============== =============== =============== =============== =====

-- This script basically aborts the transaction!!
CREATE TRIGGER abortChildDel
NO CASCADE BEFORE DELETE ON Child1
REFERENCING OLD AS t1
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE parent_name char(6) default '-1';

set parent_name= (select t2.name from parentA as t2, Child1 as t3
where t2.name=t3.name and t3.name=t1.name ); -- U can replace this with
the surrogate keys

IF parent_name is not null THEN
SIGNAL SQLSTATE 'Z0001' ('Deleting child not possible when parent is
alive');
END IF;
END@
=============== =============== =============== =============== ===

[Brian Tkatch]

Can't you just REVOKE the DELETE privelidge from the user for the child
table?

Hmm... does CASCADE work on a child TABLE where the user only has
DELETE privilidges on the parent?

B.

[shorti]

Shyam Peri wrote:

shorti,
The following trigger over the child(s) might help u out !

=============== =============== =============== =============== =====

-- This script basically aborts the transaction!!
CREATE TRIGGER abortChildDel
NO CASCADE BEFORE DELETE ON Child1
REFERENCING OLD AS t1
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE parent_name char(6) default '-1';

set parent_name= (select t2.name from parentA as t2, Child1 as t3
where t2.name=t3.name and t3.name=t1.name ); -- U can replace this with
the surrogate keys

IF parent_name is not null THEN
SIGNAL SQLSTATE 'Z0001' ('Deleting child not possible when parent is
alive');
END IF;
END@
=============== =============== =============== =============== ===

That seems to work well. I will be testing it some more but the
initial runs show it does what I need. Thanks for the info!

[Knut Stolze]

Brian Tkatch wrote:

Hmm... does CASCADE work on a child TABLE where the user only has
DELETE privilidges on the parent?

Yes, that's possible.

As long as the user has DELETE privileges on the parent table, he is not
doing anything forbidden. The FK was defined by the owner (or admin
or ...) of the child table and it says to delete the depending rows. So
the delete there is performed with the authorization of the table owner and
it runs through.

Things are different if you have ON DELETE RESTRICT/NO ACTION. Then an
explicit DELETE on the child table becomes necessary anyways, and that is
restricted as the user has not the privileges on the child table.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Brian Tkatch]

Ah, thanx. I don't have the ability to test it myself. For some reason
even the dev box here is restricted.

Perhaps then this is the best solution for the OP? I would certainly
avoid the TRIGGER is possible. Magically canceling a transaction can
cause confusion down the road.

B.

[shorti]

There were other reasons we could not revoke privileges on these table.

Also, the transaction will not be magically canceled. The failure
generates an sqlcode -438 with the SQLSTATE Z0001. I can flag it and
know precisely where the failure is.

Its good to know the CASCADE will not cause a problem if later I need
to revoke delete privileges on a table.

Thanks for the great info!

[shorti]

There were reasons in the past we did not want to revoke delete
privileges on these tables...I dont remember off hand what the concern
was but will do some research on it.

Also, the abort transaction above will not be magically canceled. The
failure generates an sqlcode -438 with the SQLSTATE Z0001. I can flag
it and know precisely where the failure is.

Its good to know the CASCADE will not cause a problem if later I need
to revoke delete privileges on a table.

Thanks for the great info!

[Brian Tkatch]

>I dont remember off hand what the concern was but will do some research on it.

Just REVOKE it and see what breaks. :P

Also, the abort transaction above will not be magically canceled. The failure generates an sqlcode -438 with the SQLSTATE
Z0001. I can flag it and know precisely where the failure is.

And that it was effected by a TRIGGER? What if a later coder needs to
find out what is happening? I've just seen too many databases where
TRIGGERs are overused. So, in general, i relegate TRIGGERs to data
maintenance (e.g. automatically populated a history TABLE) or business
rules that cannot be formed as a data rule in a CONSTRAINT.

Ultimately, you are tyring to remove a priveledge from the user, and
the database has a way of supporting that directly. So, i'd try to use
that method, if possible.

I don't mean to tell you what to do, i just want argue the idea
properly. :)

B.