“Fire” a trigger to populate columns that are NOT NULL

oracleplsqltrigger

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!

Best Answer

If you want to set values for NULLable columns, then just set those values, like this:

CREATE OR REPLACE TRIGGER enrollment_bi
BEFORE INSERT ON ENROLLMENT
FOR EACH ROW
BEGIN
  IF :NEW.STUDENT_ID    IS NULL THEN
    :NEW.STUDENT_ID    := 9;
  END IF;
  IF :NEW.SECTION_ID    IS NULL THEN
    :NEW.SECTION_ID    := 9;
  END IF;
  IF :NEW.ENROLL_DATE   IS NULL THEN
    :NEW.ENROLL_DATE   := TO_DATE('30-JAN-99','DD-MON-YY');
  END IF;
  IF :NEW.FINAL_GRADE   IS NULL THEN
    :NEW.FINAL_GRADE   := 9;
  END IF;
  IF :NEW.CREATED_BY    IS NULL THEN
    :NEW.CREATED_BY    := '9';
  END IF;
  IF :NEW.CREATED_DATE  IS NULL THEN
    :NEW.CREATED_DATE  := TO_DATE('09-SEP-99','DD-MON-YY');
  END IF;
  IF :NEW.MODIFIED_BY   IS NULL THEN
    :NEW.MODIFIED_BY   := '9';
  END IF;
  IF :NEW.MODIFIED_DATE IS NULL THEN
    :NEW.MODIFIED_DATE := TO_DATE('01-JAN-00','DD-MON-YY');
  END IF;
END;
/

There is no need to read the table before (certainly not in a FOR loop) - and that is actually forbidden. The trigger executes for each row modified by any SQL statement, and gives you the context of the operation it is invoked on: variable :OLD contains the previous values of the columns (for UPDATE and DELETES) and :NEW the new values (for UPDATE and INSERT). So all you need is to change the values in :NEW if any of them is NULL.

Then again, you do not need a trigger at all: just specify the values you want as defaults when you create the table:

CREATE TABLE ENROLLMENTS (
  STUDENT_ID    NUMBER        DEFAULT 9,
  SECTION_ID    NUMBER        DEFAULT 9,
  ENROLL_DATE   DATE          DEFAULT TO_DATE('30-JAN-99','DD-MON-YY'),
  FINAL_GRADE   NUMBER        DEFAULT 9,
  CREATED_BY    VARCHAR2(20)  DEFAULT '9',
  CREATED_DATE  DATE          DEFAULT TO_DATE('09-SEP-99','DD-MON-YY'),
  MODIFIED_BY   VARCHAR2(20)  DEFAULT '9', 
  MODIFIED_DATE DATE          DEFAULT TO_DATE('01-JAN-00','DD-MON-YY')
);

I am guessing the datatypes here - you may have different ones.

Related Question