# Exercises on triggers¶

## Exercise 1: Flight booking¶

Remember the schema from last week about flights and airports:

Airports(_code_,city)
FlightCodes(_code_, airlineName)
Flights(origin, destination, departure, arrival, _code_)
origin → Airports.code
destination → Airports.code
code → FlightCodes.code


Assume the following two tables are added to this schema: a table listing for each flight the number of available seats and the price per ticket:

AvailableFlights(_flight_, _date_, numberOfFreeSeats, price)
flight → Flights.code


And another one listing the passengers that have been booked for each flight, with the price they have paid and a unique booking reference number (an integer, to keep things simple):

Bookings(_reference_, flight, date, passenger, price)
(flight, date) → AvailableFlights.(code, date)


Question 1 Create a view that lists booking references, passengers, flight codes, date, and departure and destination cities.

Question 2 Create a trigger on the view defined in quesiton one. This trigger takes care of booking a new passenger to a flight. It is fired by an insertion of a passenger and a flight code to the view, for instance, “book Annie Adams for AF666 on 2016-03-18”. Its effect should be the following:

• if the number of free seats on AF666 at this date is positive, decrement it by one; the booking is successful
• if there are no free seats, the booking fails
• if the booking is successful, add Annie Adams and AF666 to Bookings at the given date, with the price given in AvailableFlights when booking her; also add a booking reference which is the maximum of the previous references (for all flights) plus one
• if the booking is successful, increment the price by 50 SEK for the next passenger (thus the fuller the flight, the more you pay)

Question 3 The airline decides to upgrade its database to keep track of its fleet. This means adding a table to list the available planes and updating the AvailableFlights in the following way:

Planes(_regnr_, capacity)
AvailableFlights(_flight_, _date_, numberOfFreeSeats, price, plane)
(flight, date) → Flights.(code, date)
plane → Planes.regnr


We now have duplicated information in the database: we can compute the number of free seats by subtracting the number of booked seats to the capacity of the plane. One solution would be to simply remove the numberOfFreeSeats column but, as it is often the case with production databases, it is impossible to update all the systems accessing the database at once to make use of the new schema, which means that the duplicated field needs to be present in the database during a transition period.

To make the transition easier, we would like to have the value of the numberOfFreeSeats automatically updated. In particular, if a flight becomes full well before its departure date, the airline company would like to be able to change the airplane for a bigger one. Your job is to create a trigger that automatically update numberOfSeats when this happens.

## Exercise 2: At the restaurant¶

In this exercise, we are creating the following database for a restaurant:

Tables(_number_, seats)
Bookings(_name_, _time_, nbpeople, table)
table → Tables.number


For the sake of simplicity, we assume that the database only needs to hold bookings for the current day and that bookings are always done on the hour (i.e. time is an integer between 0 and 23).

Finally, tables are always booked for two hours. This means that if table 1 is booked at 19.00, it can’t be booked at 20.00 but it can be booked again at 21.00.

Question 1 Write a view that lists the times at which tables are blocked by a booking. In the example above, where table 1 is booked at 19.00, the view should contain the following rows:

table time
1 18
1 19
1 20

Question 2 Write a trigger on the table Bookings that automatically assign a table to new rows if none is specified. The assigned table should respect the following rules:

• it should be free for the duration of the booking.
• it should be big enough for the number of people in the party
• it should be the smallest possible table to accommodate this number of people

## Exercise 3: Wiki¶

In this exercise, we are creating a database for a wiki. A wiki is a website that allows collaborative modification of its content and structure directly from the web browser (Wikipedia).

To make collaborative edition easier, we needs to keep an history of the modifications of each page. To achieve this, we will use a simple model that simply keeps each version of each page as a separate row:

PageRevision(_name_, _date_, author, text)


Question 1 To make it easier to access the wiki, creat a view Page(name, last_author, text) that shows only the latest version of each page.

Question 2 Create a trigger on your newly created view so that when a user tries to update a given page, a new revision is created instead.

Sometimes, pages on the wiki needs to be completely deleted (for instance, if a page contains sensitive information or copyrighted content). In that case, we want to remove all revisions of the page from the database but we still want to remember that the page has existed but has been deleted. To this end, we add the following table to our database:

DeleteLog(_pagename_, _date_)


Question 3 Write a trigger on the Page view such that when a page is deleted:

• all its revisions are removed from the database
• the deletion is recorded in the DeleteLog.

## Solutions¶

Material used during the tutorial: slides, handout, database setup (exercise 1), database setup (exercise 2), database setup (exercise 3).

### Exercise 1¶

Question 1
CREATE OR REPLACE VIEW Itineraries AS
SELECT
reference, passenger, flight, date,
departure.city as departure, destination.city as destination
FROM
Bookings
JOIN Flights ON Bookings.flight = Flights.code
JOIN Airports AS Departure ON departureAirport = Departure.code
JOIN Airports AS Destination ON destinationAirport = Destination.code;

Question 2
CREATE OR REPLACE FUNCTION insert_itinerary() RETURNS TRIGGER AS $$DECLARE ticket_price INTEGER; new_reference INTEGER; BEGIN -- First, check that there are at least one seat left on the flight IF NOT 0 < (SELECT numberOfFreeSeats FROM AvailableFlights WHERE flight = NEW.flight) THEN RAISE EXCEPTION 'Flight fully booked'; END IF; ticket_price := (SELECT price FROM AvailableFlights WHERE flight = NEW.flight); new_reference := ( SELECT COALESCE(MAX(reference), 0) + 1 FROM Bookings WHERE flight = new.flight ); INSERT INTO Bookings(reference, flight, date, passenger, price) VALUES (new_reference, NEW.flight, NEW.date, NEW.passenger, ticket_price); UPDATE AvailableFlights SET price = price + 1 WHERE flight = new.flight; RETURN NEW; END$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS InsertItinerary ON Itineraries;

CREATE TRIGGER InsertItinerary INSTEAD OF INSERT ON Itineraries
FOR EACH ROW EXECUTE PROCEDURE insert_itinerary();

Question 3
CREATE OR REPLACE FUNCTION update_plane() RETURNS TRIGGER AS $$DECLARE size_difference INTEGER; BEGIN size_difference := (SELECT capacity FROM Planes WHERE regnr=new.plane) - (SELECT capacity FROM Planes WHERE regnr = old.plane); if (new.numberOfFreeSeats + size_difference < 0) THEN RAISE EXCEPTION 'Plane too small!'; ELSE new.numberOfFreeSeats := new.numberOfFreeSeats + size_difference; END IF; RETURN new; END$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS UpdatePlane ON AvailableFlights;

CREATE TRIGGER UpdatePlane BEFORE UPDATE ON AvailableFlights
FOR EACH ROW
WHEN (new.plane <> old.plane)
EXECUTE PROCEDURE update_plane();


### Exercise 2¶

Question 1
CREATE OR REPLACE VIEW BlockedTables AS
SELECT tablenum, time - 1 FROM Bookings WHERE time > 0
UNION
SELECT tablenum, time FROM Bookings
UNION
SELECT tablenum, time + 1 FROM Bookings WHERE time < 23;

Question 2
CREATE OR REPLACE FUNCTION assign_table() RETURNS TRIGGER AS $$BEGIN NEW.tablenum :=( WITH possible_tables AS ( SELECT number, seats FROM tables WHERE (number, new.time) NOT IN (SELECT * FROM BlockedTables) AND seats >= new.nbpeople ) SELECT MIN(number) FROM possible_tables WHERE seats = (SELECT MIN(seats) FROM possible_tables)); IF (NEW.tablenum IS NULL) THEN RAISE EXCEPTION 'No table available'; END IF; RETURN NEW; END$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS AssignTable ON Bookings;

CREATE TRIGGER AssignTable BEFORE INSERT ON Bookings
FOR EACH ROW
WHEN (NEW.tablenum IS NULL)
EXECUTE PROCEDURE assign_table();


### Exercise 3¶

Question 1
CREATE OR REPLACE VIEW Pages AS
SELECT name, PR1.author AS last_author, PR1.text
FROM  PageRevisions AS PR1 JOIN PageRevisions AS PR2 USING (name)
GROUP BY name, PR1.date
HAVING pr1.date = MAX(pr2.date) ;

Question 2
-- ~~~ Question 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE OR REPLACE FUNCTION insert_revision() RETURNS TRIGGER AS $$BEGIN Insert into pageRevisions(name, date, author, text) values (new.name, now(), new.last_author, NEW.text); RETURN NEW; END$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS EditPage ON Pages;

CREATE TRIGGER EditPage INSTEAD OF INSERT OR UPDATE ON Pages
FOR EACH ROW
EXECUTE PROCEDURE insert_revision();

Question 3
CREATE OR REPLACE FUNCTION delete_page() RETURNS TRIGGER AS $$BEGIN DELETE FROM PageRevisions WHERE name = OLD.name; INSERT INTO deletelog VALUES (old.name, NOW()); RETURN OLD; END$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS DeletePage ON Pages;

CREATE TRIGGER DeletePage INSTEAD OF DELETE ON Pages
FOR EACH ROW
EXECUTE PROCEDURE delete_page();