CODEWITHSUNDEEP
oracle-databaseplsql
Phil
Phil

Reputation: 327

Trigger insert errors

I can't find the error in my code and the assignment is due in 2 days, I've been trying to google errors logs and everything but I just can't find what's wrong. Variables and error messages are in French, but you don't really need to understand what it means to correct the error. Thanks in advance!

The error I get after Insert Into Inscription : ORA-01403: no data found ORA-06512: a "KA791013.PASABANDONPASNOTENULLE", line 8 ORA-04088: trigger error 'KA791013.PASABANDONPASNOTENULLE'

the Tables :

     CREATE TABLE Inscription
    (codePermanent  CHAR(12)    NOT NULL,
    sigle       CHAR(7)     NOT NULL,
    noGroupe    INTEGER     NOT NULL,
    codeSession INTEGER     NOT NULL,
    dateInscription DATE        NOT NULL,
    dateAbandon DATE,
    note        INTEGER,
    CONSTRAINT SigleUnique UNIQUE(sigle),
    CONSTRAINT ClePrimaireInscription PRIMARY KEY   (codePermanent,sigle,noGroupe,codeSession),
    CONSTRAINT CERefGroupeCours FOREIGN KEY     (sigle,noGroupe,codeSession)      REFERENCES GroupeCours,
    CONSTRAINT CECodePermamentRefEtudiant FOREIGN KEY (codePermanent)              REFERENCES  Etudiant
    )
    /
    CREATE TABLE SessionUQAM
    (codeSession    INTEGER     NOT NULL,
     dateDebut  DATE        NOT NULL,
     dateFin    DATE        NOT NULL,
    CONSTRAINT C2 CHECK( dateFin = dateDebut +90),
     CONSTRAINT ClePrimaireSessionUQAM PRIMARY KEY  (codeSession)
    )
    /
  CREATE TRIGGER PasAbandonPasNoteNulle AFTER INSERT OR UPDATE OF                 dateAbandon   ON Inscription
    FOR EACH ROW
    DECLARE
lecodeSession Inscription.codeSession%TYPE;
ladateAbandon Inscription.dateAbandon%TYPE;
ladateDebut DATE;
ladateFin DATE;
lanote Inscription.note%TYPE;
BEGIN
SELECT dateDebut INTO ladateDebut
FROM SessionUQAM
WHERE SessionUQAM.codeSession = lecodeSession;
SELECT dateFin INTO ladateFin
FROM SessionUQAM
WHERE SessionUQAM.codeSession = lecodeSession;
IF (ladateAbandon IS NOT NULL AND lanote IS NOT NULL)THEN
RAISE_APPLICATION_ERROR(-20000,'Si la date abandon est non null alors la note doit etre non null');
 END IF;
IF (ladateAbandon > ladateDebut + 30) THEN
RAISE_APPLICATION_ERROR(-20000,'La date abandon doit etre de 30 jours maximum apres la date de Debut');
END IF;
IF (ladateAbandon > ladateFin)THEN
RAISE_APPLICATION_ERROR(-20000,'La date abandon doit etre avant la fin de session');
END IF;
END;
/
INSERT INTO  SessionUQAM
VALUES(32003,'01/09/2015','30/11/2015')
/
INSERT INTO Inscription
VALUES('LAVP24059100','INF3143',10,32003,'01/08/2015','30/10/2015', 42)
/

Upvotes: 0

Views: 57

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

The error is pretty clear, one of your queries finds no data; it even tells you which one as it refers to line 8, which is (counting from declare):

SELECT dateDebut INTO ladateDebut
FROM SessionUQAM
WHERE SessionUQAM.codeSession = lecodeSession;

You have declared lecodeSession but you haven't given it a value, so it is null (and nothing is equal to null).

You probably want to be referring to the codeSession value from the row beig inserted, via the NEW pseudorecord, either by assigning a value to the variable and then using it:

lecodeSession := :NEW.codeSession;

SELECT dateDebut INTO ladateDebut
FROM SessionUQAM
WHERE SessionUQAM.codeSession = lecodeSession;

or more simply (though your teach may not prefer this) directly:

SELECT dateDebut INTO ladateDebut
FROM SessionUQAM
WHERE SessionUQAM.codeSession = :NEW.codeSession;

You have the same issue with the second query too. Though you could get both values in one query anyway.

You also aren't setting ladateAbandon or lanote, so the checks that refer to those should be using :NEW.dateAbandon and :NEW.note as well. (And then remove the declarations of the three variables that you don't use, unless you go down the assignment route.)

Upvotes: 1

Related Questions