Cascade delete

[Mitchell Thomas]

I have 3 tables and they are all a one-one relationship. The tables
are in oracle with Access as the front end. If user deletes a record
from Table1, how do I programmaticall y delete the associated record
from Table2 and Table 3?

thankx,

Mitchell,

[Tom van Stiphout]

On 1 Jul 2004 19:40:30 -0700, mi******@boh.co m (Mitchell Thomas)
wrote:

Ask your Oracle DBA. She can probaly help you put a trigger in place
to perform the cascade delete at the database level. Or perhaps Oracle
has a declarative way to express cascade delete as well - I'm no
expert in that.
Doing this in Access is less desirable. If you decide to go that way
anyway, at least do it in a transaction:
pseudocode:
on error goto errorhandler
begintrans
delete * from table1 where primarykeyvalue =X
delete * from table2 where foreignkeyvalue =X
delete * from table3 where foreignkeyvalue =X
committrans
exit sub
errorhandler:
rollback
end sub

-Tom.

I have 3 tables and they are all a one-one relationship. The tables
are in oracle with Access as the front end. If user deletes a record
from Table1, how do I programmaticall y delete the associated record
from Table2 and Table 3?

thankx,

Mitchell,

[Trevor Best]

Tom van Stiphout wrote:

On 1 Jul 2004 19:40:30 -0700, mi******@boh.co m (Mitchell Thomas)
wrote:

Ask your Oracle DBA. She can probaly help you put a trigger in place
to perform the cascade delete at the database level. Or perhaps Oracle
has a declarative way to express cascade delete as well - I'm no
expert in that.
Doing this in Access is less desirable. If you decide to go that way
anyway, at least do it in a transaction:
pseudocode:
on error goto errorhandler
begintrans
delete * from table1 where primarykeyvalue =X
delete * from table2 where foreignkeyvalue =X
delete * from table3 where foreignkeyvalue =X
committrans
exit sub
errorhandler:
rollback
end sub

It may be more complex than that, if there is RI on the back-end (I'd be
suprised if there wasn't) then you'd need to delete in the reverse
order. The example given is a 1:1 relationship so we know the value to
delete from table3 but if it were 1:n relationships we don't immediately
know the pk of table3 so table2 would need to be looped.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer