Description
This laboratory includes the tasks related to implementation of advanced SQL statements and PL/SQL Trigger
Task 1 Triggers
(1) Implement and comprehensively test a statement trigger that verifies the following consistency constraint.
“Maximum number of subjects offered in a session is 7”.
Hint: use the following SQL statement to test:
INSERT INTO RunningSubject VALUES (‘CSCI236’, ‘AUTUMN’, 2015, 150);
(2) Implement and comprehensively test a row trigger that verifies the following consistency constraint.
“When inserting a new staff record, the staff number must be continuous”.
“Continuous” in staff number means, for example, that the first number must have a number 1, the second number must have a number 2, and each next number must have a
number greater by one than a number of the previous position.
When ready save your CREATE TRIGGER statement and all SQL statements that comprehensively test a trigger in a script L1.sql.
Comprehensive testing means that the trigger must reject SQL statements that violate the consistency constraint and accept SQL statements that do not violate the consistency constraint. It is a part of your task to find what SQL statements should be tested. Whenever SQL statement violates the consistency constraint a trigger must return ORA-…
error message. Use a procedure RAISE_APPLICATION_ERROR(-20001,’YOUR MESSAGE HERE’)to return ORA-… error message. If SQL statement does not violate the consistency constraint then a trigger must return no messages.
Your report must include listing of all SQL statement processed. To achieve that put the following SQL*Plus commands:
SET ECHO ON
SET FEEDBACK ON
at the beginning of SQL script L1.sql.
