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

Question

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!

Answer 1

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.

Answer 2

I believe the code snippet should be a procedure instead of trigger. Few changes would make this code working as procedure:

1. convert this into procedure from trigger
2. Remove CHECK from IF condition as CHECK is constraint and, I believe, it is not a function which can be used the way you have written.
3. Make the INSERT statement as UPDATE so that existing record of any NULL value will be updated as per your choice.

if you want to write trigger there would be a different way to write this. The BEFORE INSERT will work for the row you are inserting. So few changes suggested here for a code which will work as trigger:

1. Remove the FOR LOOP.
2. You do not have requirement of selecting any data, if I understood correctly, so remove SELECT.
3. Suppose you are checking NULL value for SECTION_ID passed from the INSERT statement, you need to write like if

IF :NEW.SECTION_ID IS NULL
THEN
:NEW.SECTION_ID := NEW VALUE

Let me know if this helps.

Thanks Sanjeeva