Hi all,
I have a db structure with a VIEW that I need to reference (ON DELETE CASCADE).
I know that it is not possible to have references on a VIEW, but maybe someone has some TRIGGERs at
hand that do this job.
Here's an example to play with:
------------------------------------------------------------------------------------------------
--
-- This table holds objects (many different sorts)
--
CREATE TABLE objekt (
id_objekt SERIAL PRIMARY KEY,
handle text
);
--
-- This table makes (some) objects buyable (products)
--
CREATE TABLE price (
id_price SERIAL PRIMARY KEY,
id_objekt int REFERENCES objekt ON DELETE CASCADE UNIQUE NOT NULL,
price float
);
--
-- This table expires some products
--
CREATE TABLE expire (
id_expire SERIAL PRIMARY KEY,
id_objekt int REFERENCES objekt ON DELETE CASCADE UNIQUE NOT NULL,
expire date NOT NULL
);
--
-- This is the virtual product table
--
CREATE VIEW product AS SELECT * FROM objekt JOIN price USING (id_objekt) LEFT OUTER JOIN expire
USING (id_objekt) WHERE expire IS NULL OR expire > now();
INSERT INTO objekt (handle) values ('product 1');
INSERT INTO objekt (handle) values ('product 2');
INSERT INTO objekt (handle) values ('product 3');
INSERT INTO price (id_objekt, price) values (1, 1.99);
INSERT INTO price (id_objekt, price) values (2, 2.99);
INSERT INTO price (id_objekt, price) values (3, 3.99);
INSERT INTO expire (id_objekt, expire) values (2, '01.01.1980');
--
-- This is what I need to be done with TRIGGERs
--
CREATE TABLE basket (
id_basket SERIAL PRIMARY KEY,
id_user int,
id_objekt int REFERENCES product ON DELETE CASCADE NOT NULL
);
-------------------------------------------------------------------------------------------------
ERROR: referenced relation "product" is not a table
TIA
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de
