using trigger to change status in one table from the modification in other table

From: Marcos Hercules Santos <mhercs(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: using trigger to change status in one table from the modification in other table
Date: 2011-09-14 12:34:37
Message-ID: 5fe81fd3-32af-4c57-a946-ffd8e0ae8e57@s2g2000vby.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello guys,

i'm trying to use trigger in these two tables below, in order to do
the following;

To cancel the booking of an accommodation, since the customer do the
host (status = "Cancelled") in the book_rooms table. Changing the
state of accommodation to "occupied" (Accommodation Table).

CREATE TABLE book_rooms
(Idbookroom int AUTO_INCREMENT not null,
IdHost int not null,
IdAccommododation int not null,
booking_date Date not null,
InitialDate Date not null,
EndDate not null,
Status varchar(10) not null check (Estado in (‘active’, ‘Cancelled’)),
PRIMARY KEY (Idbookroom),
FOREIGN KEY (IdHost) REFERENCES Hosts(IdHost),
FOREIGN KEY (IdAccommododation) REFERENCES
Accommodations(IdAccommododation),
UNIQUE(IdHost, IdAccommododation, booking_date))

TABLE Accommodations
(IdAccommododation int AUTO_INCREMENT not null,
name varchar(20) not null,
high_season_price not null numeric (5,2),
low_season_price not null numeric (5,2),
Status varchar(12) not null ((status = 'occupied') or (estado =
'available')
or (estado = 'maintenance')),
PRIMARY KEY (IdAccommododation),
UNIQUE(name)),

Any help will be appreciated

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2011-09-14 13:23:12 Re: Bit datatype performance?
Previous Message Antonio Vieiro 2011-09-14 10:00:35 Bit datatype performance?