plsql - Oracle SQLDevelopper SQL TRIGGER Creation Problem That I Can't Figure Out

Question

I want to create a trigger that manages 2 magazines, 'Elle' and 'Time'.

So if the user tries to insert a new record in the magazine table that does not match these two, the latter should not be retained.

I already have all the tables and stuff ready there's no problem in that.

The problem is that I can't figure out a way to do this properly, a friend said that this code of mine interacts with lines only, and that I need a code for the whole table and recommended that I use a Cursor.

Here's my code:

Create or replace trigger TMag
After INSERT on Magazine
FOR EACH ROW
DECLARE
e EXCEPTION;
BEGIN
IF :new.mag_nom!= 'Elle' or :new.mag_nom!= 'Time' THEN
Delete from Magazine where ISBN=:new.ISBN;
raise e;
END IF;
exception
when e then dbms_output.put_line('nom mag incorrecte');
END;

Here's a look on my tables:

CLIENT(CIN, CL_NOM, CL_ADDR, CL_VILLE, EMAIL, CONTACT_NUM);
MAGAZINE(ISBN, MAG_NOM, PRIX_Mois);
ABONNEMENT(AB_ID, #ISBN, #IN_ID, Months);
INVOICE(IN_ID, #CIN, dateI, State) ;

Thanks in advance

question from:https://stackoverflow.com/questions/65546483/oracle-sqldevelopper-sql-trigger-creation-problem-that-i-cant-figure-out

Answer 1

I think you don't need a trigger, rather resolve the problem internally by creating a CHECK Constraint such as

ALTER TABLE Magazine
ADD CONSTRAINT correcte_mag_nom  
  CHECK (mag_nom IN ('Elle', 'Time'));

If the provided value is not eligible for mag_nom column, then it will hurl as

ORA-02290 check constrain (<schema>.CORRECTE_MAG_NUM) violated