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
