DROP TABLE ENROLLMENT CASCADE CONSTRAINTS DROP TABLE SECTION

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:

1. 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

Solution

CREATE TRIGGER [dbo].[trg_total] ON [dbo].[ENROLLMENT]
AFTER UPDATE, INSERT
AS

-- First checking total students in the enrollment table.
DECLARE @total INT
SELECT @total = COUNT(*) FROM ENROLLMENT;
IF NOT (@total >= 5)
BEGIN
RAISERROR (\'SQL error, section is full\',16, 1)
ROLLBACK TRANSACTION
END