I have a problem with an UPDATE BEFORE TRIGGER

December 2, 2021

I have the following tables:

CREATE TABLE Categorie
(categ_id NUMBER(5,0),
tip VARCHAR2(20));

CREATE TABLE Reteta
(reteta_id NUMBER(3),
nume VARCHAR2(100),
descriere VARCHAR2(200),
categ_id NUMBER(3,0),
vegetariana VARCHAR2(1) CHECK (vegetariana IN ('D','N')),
timp_preparare NUMBER(20,1),
portii NUMBER(3,1));

CREATE TABLE Set_ingrediente
(reteta_id NUMBER(3,0),
ingred_id NUMBER(3,0), 
cantitate NUMBER(5,2), 
um VARCHAR2(10) NOT NULL CHECK (um IN ('gr','ml','buc','lingurita','cana')), 
comentarii VARCHAR2(100)
);

CREATE TABLE Ingredient 
(ingred_id NUMBER(3,0), 
ingredient VARCHAR2(30));

And the following primary/foreign keys:

ALTER TABLE Categorie
MODIFY (categ_id CONSTRAINT categ_id_pk PRIMARY KEY NOT NULL);

ALTER TABLE Reteta
MODIFY(CONSTRAINT reteta_id_pk PRIMARY KEY (reteta_id));

ALTER TABLE Ingredient
MODIFY(CONSTRAINT ingred_id_pk PRIMARY KEY (ingred_id));

ALTER TABLE Set_ingrediente
MODIFY( CONSTRAINT reteta_id_fk FOREIGN KEY (reteta_id) REFERENCES Reteta(reteta_id) ON DELETE CASCADE);

ALTER TABLE Set_ingrediente
MODIFY( CONSTRAINT ingred_id_fk FOREIGN KEY (ingred_id) REFERENCES Ingredient(ingred_id) ON DELETE CASCADE);

I have to write a trigger which :
Ensure that the vegetarian column cannot be changed if the recipe uses the ingredient ‘oil’.
This is the trigger which I tried and it’s not working:

CREATE OR REPLACE TRIGGER tr_oil BEFORE UPDATE
ON Reteta
FOR EACH ROW BEGIN
DECLARE
 ingredient_var ingredient.ingredient%type;
 SELECT ingredient
 INTO ingredient_var
 FROM Ingredint
 WHERE ingred_id=
 (SELECT ingred_id
 FROM Set_ingrediente
 WHERE reteta_id=NEW.reteta_id);
 IF ingredient_var='oil' THEN  
SET NEW.vegetariana = OLD.vegetariana;
END IF;
END;

>Solution :

That’s so strange; up to trigger code, everything you posted worked perfectly so I just can’t imagine what went wrong when it came to the trigger which is full of errors (misplaced begin, invalid table name, missing colon with the :new pseudorecord, …). As if two different people wrote two pieces of code you posted.

Anyway:

SQL> CREATE OR REPLACE TRIGGER tr_oil
  2    BEFORE UPDATE ON Reteta
  3    FOR EACH ROW
  4  DECLARE
  5    ingredient_var ingredient.ingredient%type;
  6  BEGIN
  7    SELECT ingredient
  8      INTO ingredient_var
  9      FROM Ingredient
 10      WHERE ingred_id = (SELECT ingred_id
 11                         FROM Set_ingrediente
 12                         WHERE reteta_id = :NEW.reteta_id);
 13
 14    IF ingredient_var='oil' THEN
 15       :NEW.vegetariana := :OLD.vegetariana;
 16     END IF;
 17  END;
 18  /

Trigger created.

SQL>