Re: Updatable views

Celko,

I know about Instead OF triggers.
While I can see that it is a solution I rather implement a dotnet UpdatableView class which handles all about every view I can make, so that I dont have to write Instead of triggers again and again.

On 7 Dec 2005 19:32:48 -0800, "-CELKO-" <jcelko212_at_earthlink.net> wrote:

>Once you have a join in a VIEW it cannot be updated directly; you will
>need to use INSTEAD OF triggers.
>
>Get a copy of TREES & HIERARCHIES IN SQL for several solutions for
>hierarchies.
>

I could be mistaken but I have the impression that that topic is about the opposite: putting trees in a database. What I want is 180 different: I want to get an updatable tree from a RDBMS given whatever definition of a tree and whatever kind of database definition. Thats a code challenge much more then a SQL challenge.

What you and the book and the topics in this newsgroup about trees in databases say doesnt seem to apply if you want to work from the relational definition.

Thanks anyway

Rick
>The classic scenario calls for a root class with all the common
>attributes and then specialized sub-classes under it. As an example,
>let's take the class of Vehicles and find an industry standard
>identifier (VIN), and add two mutually exclusive sub-classes, Sport
>utility vehicles and sedans ('SUV', 'SED').
>
>CREATE TABLE Vehicles
>(vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) NOT NULL
> CHECK(vehicle_type IN ('SUV', 'SED')),
> UNIQUE (vin, vehicle_type),
> ..);
>
>Notice the overlapping candidate keys. I then use a compound candidate
>key (vin, vehicle_type) and a constraint in each sub-class table to
>assure that the vehicle_type is locked and agrees with the Vehicles
>table. Add some DRI actions and you are done:
>
>CREATE TABLE SUV
>(vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
> CHECK(vehicle_type = 'SUV'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Vehicles(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
>CREATE TABLE Sedans
>(vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
> CHECK(vehicle_type = 'SED'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Vehicles(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
>I can continue to build a hierarchy like this. For example, if I had a
>Sedans table that broke down into two-door and four-door sedans, I
>could a schema like this:
>
>CREATE TABLE Sedans
>(vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
> CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Vehicles(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
>CREATE TABLE TwoDoor
>(vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL
> CHECK(vehicle_type = '2DR'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Sedans(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
>CREATE TABLE FourDoor
>(vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL
> CHECK(vehicle_type = '4DR'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Sedans (vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
>The idea is to build a chain of identifiers and types in a UNIQUE()
>constraint that go up the tree when you use a REFERENCES constraint.
>Obviously, you can do variants of this trick to get different class
>structures.
>
>If an entity doesn't have to be exclusively one subtype, you play with
>the root of the class hierarchy:
>
>CREATE TABLE Vehicles
>(vin CHAR(17) NOT NULL,
> vehicle_type CHAR(3) NOT NULL
> CHECK(vehicle_type IN ('SUV', 'SED')),
> PRIMARY KEY (vin, vehicle_type),
> ..);
>
>Now start hiding all this stuff in VIEWs immediately and add an INSTEAD
>OF trigger to those VIEWs.
Received on Thu Dec 08 2005 - 19:42:47 CET