Working out of my SQL book at the moment. The question is, Create a trigger based upon the ENROLLMENT table. The trigger should “fire” before an INSERT statement. The trigger will populate all the columns that are NOT NULL and columns that have foreign key constraints.
CREATE OR REPLACE TRIGGER triggerOne BEFORE INSERT ON ENROLLMENT FOR EACH ROW BEGIN --loop to get all of our columns that are are checked to be not NULL FOR i in (SELECT STUDENT_ID ,SECTION_ID ,ENROLL_DATE ,FINAL_GRADE ,CREATED_BY ,CREATED_DATE ,MODIFIED_BY , MODIFIED_DATE FROM ENROLLMENT WHERE created_by || created_date || enroll_date || final_grade || modified_by || modified_date || section_id || student_id IS NOT NULL) --loop for CHECK constraints from ENROLLMENT table LOOP IF (CHECK(:NEW.SECTION_ID = ENR_SECT_FK) AND CHECK(:NEW.STUDENT_ID = ENR_STU_FK)) THEN INSERT INTO ENROLLMENT(9,9,'30-JAN-99',9,'9','09-SEP-99','9','01-JAN-00'); END IF; END LOOP; END;
I'm getting an error on line 9
Error(9,5): PLS-00103: Encountered the symbol "CHECK" when expecting one of the following: ( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe
We have a ENROLLMENT table with constraints for the columns that are NOT NULL and constraints ENR-SECT_FK and ENR_STU_FK as the foreign key constraints.
Not sure where I'm going wrong here... going to re-read the chapter, but any help would be greatly appreciated!