Trigger, Views or something else?

53 Views Asked by At

I have some tables to describe a trips. Table stops keeps intermediate stops of trips. Table trip has an attribute type witch indicates on which continent the trip was made.

CREATE TABLE continent (
    id_continent SERIAL PRIMARY KEY,    
    name VARCHAR(30) NOT NULL UNIQUE    /* like 'europe', 'asia' etc */
);

CREATE TABLE port (
    id_port SERIAL PRIMARY KEY,
    id_continent INT,  
    FOREIGN KEY (id_continent) REFERENCES continent (id_continent)
);

CREATE TABLE trip (
    id_trip SERIAL PRIMARY KEY,
    type VARCHAR(10) CHECK (type IN ('europe', 'asia', 'africa', 'inter'))
);

CREATE TABLE stops (
    id_stop SERIAL PRIMARY KEY,
    id_trip INT,
    id_port INT,
    FOREIGN KEY (id_trip) REFERENCES trip (id_trip),
    FOREIGN KEY (id_port) REFERENCES port (id_port)
);

also I've create view view_stops_continents as join continent, port and stops which made :

id_trip  id_port id_continent
-------+--------+-------------
10     |  16    |   1
10     |  7     |   1
10     |  54    |   1
12     |  91    |   2
12     |  12    |   1
32     |  11    |   3
32     |  24    |   3

so I need to UPDATE field type in table trip according this view I mean if for id_trip all id_continent are the same set type as name of this continent, else set type 'inter' for example here for id_trip = 10 type will be 'europe' and for id_trip = 12 it will be 'inter' and the table trip from

SELECT * FROM trip;

id_trip   type   
-------+--------
 10    | 
 12    | 
 32    |

should become

SELECT * FROM trip;

id_trip   type   
-------+--------
 10    | 'europe'
 12    | 'inter'
 32    | 'asia'

how can I do it?

UPD: I try to make it by views, for example I've made this one:

   id_trip    type 
-----------+--------------            
        10 | Europe
        32 | Africa
        12 | Asia
        12 | Europe

how to use it to set attribute type in table trip ?

0

There are 0 best solutions below