Re: delete cascade

Bob Badour wrote:
...
> In a second case, consider an application view that groups the invoice
> items as an RVA of each invoice. If one deletes an invoice, one deletes
> the invoice items at the same time and the dbms will not balk.
>
> In a third case, consider an application view that exposes only the
> invoice and not the items: The "on delete cascade" trigger would allow
> one to delete the invoice and the invoice items without ever knowing
> about the items. Can one devise a view that would achieve the same effect?
> ...

I think it might be "(invoices join items){invoice#}". However, this might not gibe with people who say a view must behave exactly like a base. That wouldn't bother me as I think the two have differences.

I presume you muse about views for a practical as well as a theoretical reason - in an existing product, they might give the effect of being able to delete from two tables with a single delete statement, provided the product's definition of deletion causes it to be enacted against all the tables mentioned in a view expression?

If so and if the second constraint you suggested early on didn't apply, some invoices might not have items, a view expressiono for that might be something like "(invoices join items){invoice#} UNION invoices". If the second constraint didn't apply, one could delete from invoices without the view, but I suppose a possible advantage of the view is that it would work in any case.

> Would it suffice to declare the view using the RVA as above and then
> project away the RVA?

I'm guessing that you mention rva's so that there can be no chance that a product would be permitted to only partially complete the operation, and leave some items dangling, whereas some products might allow a series of delete statements to be interrupted?

p Received on Sun Apr 22 2007 - 23:25:45 CEST