Question

Given the following three tables:

Customer(CustId, CustName, PhoneNum)

Customer:

CustId	CustName	PhoneNum
1	Ahmad Ali	0555555555
2	Sara Ahmad	0544444444
3	Mona Saad	0533333333

Tabless(TableNum, NumOfSeats)

Tabless:

TableNum	NumOfSeats
1	4
2	4
3	2
4	6

Bookings(BookingNum, CustId, BDay, BMonth, BYear, BHour, NumOfPeople, TableNum)

Bookings:

BookingNum	CustId	BDay	BMonth	BYear	BHour	NumOfPeople	TableNum
1	2	10	12	2019	7	2	3
2	3	30	12	2019	10	4	2
3	1	1	1	2020	11	5	4
4	3	10	12	2019	5	2	3

Hint:

• Each booking should start at exact time.

For example: Booking number 1 start at 7:00 pm.

• Also, each customer is allowed to stay for maximum of 2 hours in the restaurant.

For example: Booking number 1 start at 7:00 pm and end at 9:00 pm.

Write SQL queries using SQL developer to do the following:

1. Create a trigger that does not allow updating number of people reserved to more than the number of seats in the selected table.
2. Update one of the bookings to number of people more than the number of seats for that table.

Answer 1

Customer Table -

Tabless Table -

Bookings Table -

Create a trigger that does not allow updating number of people reserved to more than the number of seats in the selected table.

Trigger Query -

CREATE TRIGGER TriggerBooking
ON Bookings
AFTER UPDATE
AS
DECLARE @nrOfViolations int
SELECT @nrOfViolations = COUNT(*) FROM deleted d
JOIN Bookings t ON d.BookingNum = t.BookingNum
   JOIN Tabless tt ON t.tablenum = tt.TableNum
WHERE t.NumOfPeople > tt.NumOfSeats

IF @nrOfViolations > 0
BEGIN
RAISERROR('Number of people should be less than number of seat', 16, 1);
ROLLBACK TRANSACTION
END

RUN TO TEST :
UPDATE Bookings SET NumOfPeople=7 WHERE BookingNum=1

RESULT :

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

Update one of the bookings to number of people more than the number of seats for that table.

Trigger will not allow update query for people more than number of seats, we have to disable the trigger on Booking table and then have to run update query.

ALTER TABLE Bookings DISABLE TRIGGER TriggerBooking

UPDATE Bookings SET NumOfPeople=7 WHERE BookingNum=1

RESULT :

Thank you.