I'd like to hear your thoughts on best practicies for using a Delete
trigger for managing Master/Detail relationships.



--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Michael,

> I'd like to hear your thoughts on best practicies for using a Delete
> trigger for managing Master/Detail relationships.

That's quite simple. Use a trigger like that:

SCRIPT
BEGIN
EXECUTE IMMEDIATE 'CREATE TRIGGER "DeleteScheine" AFTER DELETE ON
"MainTable"
BEGIN

  Execute Immediate ''delete from detail1 where ID= ?'' using  oldrow.ID;

  Execute Immediate ''delete from detail2 where ID= ?'' using  oldrow.ID;

END

DESCRIPTION ''Deletes all matching records in detail tables.''';
END

Uli

Pretty much how I do it

Roy Lambert

Michael,

Assuming that you have already declared a Foreign Key that prevents any deletes on the master table if there are detail rows, what you have to do is to delete the detail rows in a 'Before Delete' trigger (on the master table).

Be aware that you will have to ensure that *all DELETEs run inside a transaction* .
It's not that hard if your users don't have direct access to the database with EDB Manager  and can only touch the database from your applications, because if they do they can directly DELETE rows in an unsafe way.

As far as I know Tim wants to add nested transactions and cascading updates and deletes support to EDB before V3, and I eagerly hope that he can even do that .

--
Fernando Dias
[Team Elevate]

Uli, Roy

An AFTER DELETE trigger wont work if there are Foreign Key constraints defined - it will prevent you from deleting the master while there are still detail rows.
On the other hand, if there are no FK constraints, then you will have to deal with a lot of other issues in code, like preventing detail rows from being inserted if there is no corresponding master, as well as to ensure that updates on the master or the detail tables wont produce "orphan" rows. One way or the other, all the deletes involving more than one row have to be enclosed in a transaction to be safe.

--
Fernando Dias
[Team Elevate]

Fernando


>An AFTER DELETE trigger wont work if there are Foreign Key constraints defined - it will prevent you from deleting the master while there are still detail rows.
>On the other hand, if there are no FK constraints, then you will have to deal with a lot of other issues in code, like preventing detail rows from being inserted if there is no corresponding master, as well as to ensure that updates on the master or the detail tables wont produce "orphan" rows. One way or the other, all the deletes involving more than one row have to be enclosed in a transaction to be safe.

I understand the concept of database enforced referential integrity but you would have to use a gun at my head to get me to use it these days. I've suffered too often in the past to use it if I have a viable option. So strangely enough I don't have any Foreign Key constraints defined. I don't have anywhere in any system I've ever built where its possible for the user to enter detail rows if a master is needed and is missing. In my highly personal view any system that does allow that should be removed very rapidly.

Its a bit like my stand on null<>emptystring - I'll fight to the death to use program enforced RI rather than database enforced RI

The only way I will use database enforced RI is if there is a simple switch I can flip to say "stop enforcing RI while I sort this bloody mess out"

Roy Lambert

ps If you've never had a system crash on you and found that the last three backups were corrupt and that you had to find someway to fix things so a lot of work didn't disappear you won't understand.

Fernando,

<< As far as I know Tim wants to add nested transactions and cascading
updates and deletes support to EDB before V3, and I eagerly hope that he can
even do that . >>

Yes, that is correct.  At the very least, nested transactions so that doing
the cascading updates and deletes in a trigger isn't as complicated as it is
now.

Tim Young
Elevate Software
www.elevatesoft.com

>>The only way I will use database enforced RI is if there is a simple switch I can flip to say "stop enforcing RI while >>I sort this bloody mess out"

!!! I am with you on this one Roy! DBA's love to be in control, but I find systems with lots of RI end up needing lots of "ALTER TABLE DROP CONSTRAINT ... " type code to be written.

If users are accessing the database from many different applications (i.e. Delphi App + ODBC links via Excel + Website ...) then it is more secure to control all the constraints from the DB ... but it is more painful.

Adam


>>>The only way I will use database enforced RI is if there is a simple switch I can flip to say "stop enforcing RI while >>I sort this bloody mess out"
>
>!!! I am with you on this one Roy! DBA's love to be in control, but I find systems with lots of RI end up needing lots of "ALTER TABLE DROP CONSTRAINT ... " type code to be written.
>
>If users are accessing the database from many different applications (i.e. Delphi App + ODBC links via Excel + Website ...) then it is more secure to control all the constraints from the DB ... but it is more painful.

Unfortunately I have to agree about the need for DB enforced RI when there are multiple applications UPDATING the tables.

Roy