Mega Code Archive

A Trigger to check the available room

SQL> SQL> SQL> CREATE TABLE place ( 2 room_id NUMBER(5) PRIMARY KEY, 3 building VARCHAR2(15), 4 room_number NUMBER(4), 5 number_seats NUMBER(4), 6 description VARCHAR2(50) 7 ); Table created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description) 2 VALUES (20001, 'Building 7', 201, 1000, 'Large Lecture Hall'); 1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description) 2 VALUES (20002, 'Building 6', 101, 500, 'Small Lecture Hall'); 1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description) 2 VALUES (20003, 'Building 6', 150, 50, 'Discussion Room A'); 1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description) 2 VALUES (20004, 'Building 6', 160, 50, 'Discussion Room B'); 1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description) 2 VALUES (20005, 'Building 6', 170, 50, 'Discussion Room C'); 1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description) 2 VALUES (20006, 'Music Building', 100, 10, 'Music Practice Room'); 1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description) 2 VALUES (20007, 'Music Building', 200, 1000, 'Concert Room'); 1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description) 2 VALUES (20008, 'Building 7', 300, 75, 'Discussion Room D'); 1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description) 2 VALUES (20009, 'Building 7', 310, 50, 'Discussion Room E'); 1 row created. SQL> CREATE TABLE session ( 2 department CHAR(3), 3 course NUMBER(3), 4 description VARCHAR2(2000), 5 max_lecturer NUMBER(3), 6 current_lecturer NUMBER(3), 7 num_credits NUMBER(1), 8 room_id NUMBER(5) 9 ); Table created. SQL> SQL> CREATE OR REPLACE TRIGGER CheckRoomID 2 BEFORE INSERT OR UPDATE OF room_id 3 ON session 4 FOR EACH ROW 5 DECLARE 6 v_RoomID NUMBER(5); 7 BEGIN 8 SELECT room_id 9 into v_RoomID 10 FROM place 11 where room_id = :new.room_id; 12 EXCEPTION 13 WHEN NO_DATA_FOUND THEN 14 RAISE_APPLICATION_ERROR(-20000, :new.room_id || ' is not a ' || 15 ' valid room'); 16 END CheckRoomID; 17 / Trigger created. SQL> SQL> SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id) 2 VALUES ('HIS', 101, 'History 101', 30, 11, 4, 20000); INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id) * ERROR at line 1: ORA-20000: 20000 is not a valid room ORA-06512: at "RNTSOFT.CHECKROOMID", line 10 ORA-04088: error during execution of trigger 'RNTSOFT.CHECKROOMID' SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id) 2 VALUES ('HIS', 301, 'History 301', 30, 0, 4, 20004); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id) 2 VALUES ('CS', 101, 'Computer Science 101', 50, 0, 4, 20001); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id) 2 VALUES ('ECN', 203, 'Economics 203', 15, 0, 3, 20002); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id) 2 VALUES ('CS', 102, 'Computer Science 102', 35, 3, 4, 20003); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id) 2 VALUES ('MUS', 410, 'Music 410', 5, 4, 3, 20005); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id) 2 VALUES ('ECN', 101, 'Economics 101', 50, 0, 4, 20007); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id) 2 VALUES ('NUT', 307, 'Nutrition 307', 20, 2, 4, 20008); 1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id) 2 VALUES ('MUS', 100, 'Music 100', 100, 0, 3, NULL); INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id) * ERROR at line 1: ORA-20000: is not a valid room ORA-06512: at "RNTSOFT.CHECKROOMID", line 10 ORA-04088: error during execution of trigger 'RNTSOFT.CHECKROOMID' SQL> SQL> SQL> SQL> drop table session; Table dropped. SQL> SQL> drop table place; Table dropped.