Trigger LMD - PL/SQL

Question

I would like to execute the trigger below in order for the error to be raised("You are not allowed to change the date"), that should be triggered when someone tries to change the date in the table, but I can't manage to find out how I can call the trigger. I did try SET DataValiditate = TO_DATE(DataValiditate, 'yyyy/mm/dd') + '2023/10/14';, but it doesn't work. Please could you help me? Thank you!

CREATE TABLE ProduseAchizitionate (
 ID_Achizitii NUMBER(9) NOT NULL,
 CodProdus NUMBER(9) NOT NULL,
 Cantitate NUMBER(9) NOT NULL,
 DataValiditate DATE NOT NULL,
 CONSTRAINT pk_ProduseAchizitionate PRIMARY KEY(ID_Achizitii)
);


ALTER TABLE ProduseAchizitionate
ADD CONSTRAINT "include" FOREIGN KEY (ID_Achizitii) 
REFERENCES Achizitii(ID_Achizitii) ON DELETE CASCADE;

ALTER TABLE ProduseAchizitionate
 ADD CONSTRAINT "cuprinde" FOREIGN KEY (CodProdus) 
 REFERENCES Medicament(CodProdus) ON DELETE CASCADE;
INSERT ALL
 INTO ProduseAchizitionate(ID_Achizitii, CodProdus, Cantitate, 
 DataValiditate) 
 VALUES(01,04,40,TO_DATE('2023/10/10','yyyy/mm/dd'))
SELECT * FROM dual;


CREATE OR REPLACE TRIGGER produseAchizitionate_update
BEFORE UPDATE OF DataValiditate ON ProduseAchizitionate
FOR EACH ROW
BEGIN
IF (:NEW.DataValiditate <> :OLD.DataValiditate) THEN
  RAISE_APPLICATION_ERROR(-20201,'Data expirarii produsului nu poate fi modificata!');
END IF;
END;
/

UPDATE ProduseAchizitionate
SET DataValiditate = TO_DATE(DataValiditate, 'yyyy/mm/dd');

user_12913491 · Answer

As shown, your UPDATE isn't changing the value of the date at all, so the trigger won't evaluate the error condition. Try replacing your to_date with SYSDATE, or TO_DATE('2023/10/14', 'yyyy/mm/dd'):

CREATE TABLE ProduseAchizitionate (
 ID_Achizitii NUMBER(9) NOT NULL,
 CodProdus NUMBER(9) NOT NULL,
 Cantitate NUMBER(9) NOT NULL,
 DataValiditate DATE NOT NULL,
 CONSTRAINT pk_ProduseAchizitionate PRIMARY KEY(ID_Achizitii)
);
   
INSERT ALL
 INTO ProduseAchizitionate(ID_Achizitii, CodProdus, Cantitate, 
 DataValiditate) 
 VALUES(01,04,40,TO_DATE('2023/10/10','yyyy/mm/dd'))
SELECT * FROM dual;

CREATE OR REPLACE TRIGGER produseAchizitionate_update
BEFORE UPDATE OF DataValiditate ON ProduseAchizitionate
FOR EACH ROW
BEGIN
IF (:NEW.DataValiditate <> :OLD.DataValiditate) THEN
  RAISE_APPLICATION_ERROR(-20201,'Data expirarii produsului nu poate fi modificata!');
END IF;
END;
/

UPDATE ProduseAchizitionate SET DataValiditate = SYSDATE;

Output:

Table PRODUSEACHIZITIONATE created.


1 row inserted.


Trigger PRODUSEACHIZITIONATE_UPDATE compiled


Error starting at line : 37 in command -
UPDATE ProduseAchizitionate SET DataValiditate = SYSDATE
Error report -
ORA-20201: Data expirarii produsului nu poate fi modificata!
ORA-06512: at "PETE.PRODUSEACHIZITIONATE_UPDATE", line 3
ORA-04088: error during execution of trigger 'PETE.PRODUSEACHIZITIONATE_UPDATE'

Trigger LMD - PL/SQL

1 Answers