0

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 use TO_DATE() function or DATE literals, e.g. DATE '2099-01-30' (or DATE '1999-01-30'?) – Wernfried Domscheit May 2 at 14:12
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.

  • This logically makes sense to me, however, when testing the trigger using a INSERT statement, I'm getting an error '"integrity constraint (%s.%s) violated - parent key not found"'. My insert statement was just inserting NULL for all columns to test if the trigger would fire. – Bobby Burke May 2 at 16:23
  • I see, and I also understand now why you mentioned foreign keys in your original post: you have STUDENT_ID and SECTION_ID as foreign keys to other tables ... So when you set them both to NULL, the database will check if those values exist in the tables your foreign key references ... So unless you create a dummy student with STUDENT_ID = 9 and a dummy section with SECTION_ID = 9, your trigger (or just the defaults above) will make inserts fail if you do not specify a valid student or section. – Albert Godfrind May 3 at 17:55
  • The very reason for having foreign keys is to make sure your data is consistent - i.e. that any enrolment record is always for a student and a section that exist. Also in your case the primary key of your table is (STUDENT_ID, SECTION_ID) since you only want a student enrolling once at most in a section. That actually prevents you from inserting NULLs in either of the columns. – Albert Godfrind May 3 at 17:58
  • I would also question the wisdom of setting the other columns (ENROLL_DATE etc) to default values. Why is that ? The purpose of NULLs is to clearly identify those columns that are empty - typically the MODIFIED_DATE and MODIFIED_BY columns that should be NULL initially. Same for FINAL_GRADE. If you want to use defaults for CREATED_BY and CREATED_DATE that would be the current user (USER) and current date (CURRENT_DATE) - and that is best achieved using DEFAULT clauses. – Albert Godfrind May 3 at 18:02
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.