What's wrong with this PL/SQL Trigger?

user_17715650 0
Viewed 120

I have this table, and I want to create a trigger on Magazine, that verifies "after insert" if the name of the Magazine inserted is either Vogue or People.

If it's not one of them, it gets deleted.

Table:

  • MAGAZINE (ISBN, MAG_NOM, PRIX_Mois);

My trigger:

CREATE OR REPLACE TRIGGER TMag
  AFTER INSERT ON Magazine
  FOR EACH ROW
DECLARE
  e EXCEPTION;
BEGIN
  IF :new.mag_nom != 'Vogue' or :new.mag_nom != 'People' THEN
     DELETE Magazine WHERE ISBN = :new.ISBN;
    RAISE e;
  END IF;
EXCEPTION
  WHEN e THEN
    DBMS_OUTPUT.PUT_LINE('nom mag incorrecte');
END;

But the problem is my teacher told me:

This is not suitable for every situation

I don't know what that means, can you please help me improve this trigger?

It seemed correct to me, what did I do wrong ?

triggers plsql oracle sql
1 Answers

You don't need to use a DML, convert the trigger into this

CREATE OR REPLACE TRIGGER TMag
  AFTER INSERT ON Magazine
  FOR EACH ROW
BEGIN
  IF :new.mag_nom NOT IN ('Vogue','People') THEN
    RAISE_APPLICATION_ERROR(-20202,'nom mag incorrecte !');
  END IF;
END;
/

and you would get table is mutating error in this case due to using the table, on which the trigger is created, within the trigger's body.

Moreover it would be far better to add a check constraint than creating a trigger such as

ALTER TABLE abc
ADD CONSTRAINT cc_mag_nom
  CHECK (mag_nom IN ('Vogue','People'));
user_58413060
Related