PL/SQL Trigger not finding values to analyze-CodePudding

I'm creating a PL/SQL Trigger that gives me an error if the date_fin < date_begin. (Im new to triggers.)

My problem is that when testing Im getting ORA-01403: no Data found. Id say the trigger has found no data to analyze but my table contains values.

Does my error come from my trigger format?

Here is my code:

--TRIGGER1
CREATE OR REPLACE TRIGGER verifDate
BEFORE INSERT OR UPDATE ON PROJET FOR EACH ROW
DECLARE date_debut projet.dateDebut%TYPE;
date_fin projet.dateFin%TYPE;
pragma autonomous_transaction;
BEGIN
    Select projet.dateDebut into date_debut from projet where date_debut=:new.dateDebut;
    Select projet.dateFin into date_fin from projet where date_fin=:new.datefin;
        IF date_fin < date_debut
        THEN
            RAISE_APPLICATION_ERROR(-20100, 'Mauvaise date') ;
        END IF;
END;

Tests:

--TEST1.1
INSERT INTO PROJET (idProjet, idClient, nomProjet, dateDebut, dateFin, idResponsable)
VALUES (4,355,'Projet Test', '01/12/2020', '30/12/2019', 3851);


--TEST1.2
UPDATE PROJET
SET dateFIN = '01/09/2003' , dateDebut = '31/12/2019'
WHERE Projet.idProjet=2;

CodePudding user response：

Yes, the error comes from the trigger because it is a BEFORE INSERT trigger so the row does not yet exist in the table but you are trying to use SELECT to get data matching the row and it fails with a NO_DATA_FOUND exception.

Instead, you should use the :NEW (and, if you need it, :OLD) record:

CREATE OR REPLACE TRIGGER verifDate
  BEFORE INSERT OR UPDATE ON PROJET FOR EACH ROW
BEGIN
  IF :NEW.dateFIN < :NEW.dateDebut THEN
    RAISE_APPLICATION_ERROR(-20100, 'Mauvaise date') ;
  END IF;
END verifDate;
/

However, this should be done using a CHECK constraint:

ALTER TABLE projet ADD CONSTRAINT projet__datedebut_lte_datefin__chk
  CHECK (dateDebut < dateFin);

db<>fiddle here