Hi guys,

I'm thinking of how could solve this problem with trigger. I'm working on this two tables

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)),


I'm trying to use trigger to do the following,

cancel the booking of an accommodation, since the customer do the host (status = "Cancelled"). Changing the state of accommodation to "occupied".


no success till now.

pls. post your question with a sample data, as to what is the input data and where is the effect ?

BTW : triggers are well established concept, the only question here is the langauge although pl/sql is popular, still many algorithms are also developed in C language

Postgresql supports both c and pl/sql