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!
30-JAN-99
is a string rather than a DATE. Better useTO_DATE()
function or DATE literals, e.g.DATE '2099-01-30'
(orDATE '1999-01-30'
?)