How to create such a view in Oracle 8 ?

Hi there,

I got the following problem:

I got 3 Tables, let them be called Dealer, Offer, Demand. They are set up in the database as follows:

create table dealer (

   iDealerId integer PRIMARY KEY,
   vcName varchar(80)
);

create table offer (

   iOfferId integer PRIMARY KEY,
   iDealerId integer REFERENCES dealer(iDealerId),    vcOffer varchar(80)
);

create table nachfrage (

   iDemandId integer PRIMARY KEY,
   iDealerId integer REFERENCES dealer(iDealerId),    vcDemand varchar(80)
);

If I want to delete a dealer from the dealer's table, I first would like to check whether there are some offers or demands from this dealer. This I want to have in a seperate table or view, let it be named "dealerdeletable".

Now, is it possible to create a view, that contains a row for each dealer, having its dealerid and a "1" if there are no offers and demands for this dealer and a "0" otherwise ?

Or, is it more reasonable to create such a table by a "on delete, insert, update"-trigger defined on the tables demand and offer ?

I appreciate any help

Thanks and regards,
Marcus Received on Sun May 30 1999 - 15:59:19 CDT