Part 2. (Triggers. 60 points) Consider the SECTION and ENROLLMENT tables defined by the following script, which also populates the SECTION table;

DROP TABLE ENROLLMENT CASCADE CONSTRAINTS;

DROP TABLE SECTION CASCADE CONSTRAINTS;

CREATE TABLE SECTION(

SectionID CHAR(5),

Course VARCHAR2(7),

Students NUMBER DEFAULT 0,

CONSTRAINT PK_SECTION

PRIMARY KEY (SectionID)

);

CREATE TABLE ENROLLMENT(

SectionID CHAR(5),

StudentID CHAR(7),

CONSTRAINT PK_ENROLLMENT

PRIMARY KEY (SectionID, StudentID),

CONSTRAINT FK_ENROLLMENT_SECTION

FOREIGN KEY (SectionID)

REFERENCES SECTION (SectionID)

);

INSERT INTO SECTION (SectionID, Course) VALUES ( '12345', 'CSC 355' );

INSERT INTO SECTION (SectionID, Course) VALUES ( '22109', 'CSC 309' );

INSERT INTO SECTION (SectionID, Course) VALUES ( '99113', 'CSC 300' );

INSERT INTO SECTION (SectionID, Course) VALUES ( '99114', 'CSC 300' );

COMMIT;

SELECT * FROM SECTION;

The Students attribute of SECTION should store a count of how many students are enrolled in the section that is, the number of records in ENROLLMENT with that SectionID and its value should never exceed five. (They are very small sections) Your task is to write three triggers that will maintain the value of the Students attribute as changes are made to the ENROLLMENT table.

Write definitions of the following three triggers:

A. Write a trigger that will fire when a user attempts to INSERT a row into ENROLLMENT. This trigger will check the value of SECTION.Students for the corresponding section. If SECTION.Students is less than 5, then there is still room in the section so allow the insert and update SECTION.Students. If SECTION.Students is equal to 5, then the section is full so it will cancel the INSERT and display an error message stating that the section is full.

Sample Data:

INSERT INTO ENROLLMENT VALUES ('12345', '1234567');

INSERT INTO ENROLLMENT VALUES ('12345', '2234567');

INSERT INTO ENROLLMENT VALUES ('12345', '3234567');

INSERT INTO ENROLLMENT VALUES ('12345', '4234567');

INSERT INTO ENROLLMENT VALUES ('12345', '5234567');

INSERT INTO ENROLLMENT VALUES ('12345', '6234567');

SELECT * FROM Section;

SELECT * FROM Enrollment;

The last insert should return an error message that looks like:

Error starting at line : 27 in command -

INSERT INTO ENROLLMENT VALUES ('12345', '6234567')

Error report -

SQL Error: ORA-20200: Section is full.

ORA-06512: at "JWAGNE32.ADDSTUDENT", line 14

ORA-04088: error during execution of trigger 'JWAGNE32.ADDSTUDENT'

The output from the SELECT queries should look like:

SECTIONID COURSE STUDENTS

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

12345 CSC 355 5

22109 CSC 309 0

99113 CSC 300 0

99114 CSC 300 0

SECTIONID STUDENTID

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

12345 1234567

12345 2234567

12345 3234567

12345 4234567

12345 5234567

B. Write a trigger that will fire when a user attempts to DELETE one or more rows from ENROLLMENT. This trigger will update the values of SECTION.Students for any affected sections to make sure they are accurate after the rows are deleted, by decreasing the value of SECTION.Students by one each time a student is removed from a section.

Sample Data:

DELETE FROM ENROLLMENT WHERE StudentID = '1234567';

SELECT * FROM Section;

SELECT * FROM Enrollment;

The output from the SELECT queries should look like:

SECTIONID COURSE STUDENTS

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

12345 CSC 355 4

22109 CSC 309 0

99113 CSC 300 0

99114 CSC 300 0

SECTIONID STUDENTID

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

12345 2234567

12345 3234567

12345 4234567

12345 5234567

C. The third trigger, named NoChanges, will fire when a user attempts to UPDATE one or more rows of ENROLLMENT. The trigger will cancel the UPDATE and display an error message stating that no updates are permitted to existing rows of ENROLLMENT.

Sample Data:

UPDATE Enrollment

SET StudentID = '7654321'

WHERE StudentID = '4234567';

The output from the UPDATE query should look like:

Error starting at line : 59 in command -

UPDATE Enrollment

SET StudentID = '7654321'

WHERE StudentID = '4234567'

Error report -

SQL Error: ORA-20201: Update is not allowed ENROLLMENT.

ORA-06512: at "JWAGNE32.UPDATESTUDENT", line 2

ORA-04088: error during execution of trigger 'JWAGNE32.UPDATESTUDENT'

Hint: EnrollStudent and WithdrawStudents should be row-level triggers; NoChanges should be a statement-level trigger.

Run the script to define your triggers and test them to make sure they work by doing a few INSERTS, DELETES, and UPDATES on the ENROLLMENT table.