Trigger prevents table change

Asked

Viewed 353 times

1

I need to make a Rigger that prevents the same employee from being part of more than one crew on the same day.

The crew table is as follows:

Tripulação = {id_Voo, data, id_Funcionário }

The 3 attributes are together the primary key.

What I want to do here is to see if it already exists in the same table row Tripulação the :new.id_Funcionário and :new.data.

oracle pl-sql trigger

  • I believe it is enough to define these two fields as Unique to solve the problem. You don’t need Trigger.

  • @Earendul is for academic work

  • Won’t that work for you? http://www.experts-exchange.com/questions/21270418/Check-if-exists-trigger.html#a13018305

  • You need to create an account to see this answer.

  • Yeah, before I could see, now I can’t see :(

1 answer

1

Trigger would look like this.

CREATE OR REPLACE TRIGGER NOME_DA_TRIGGER
BEFORE INSERT OR UPDATE ON TRIPULACAO
FOR EACH ROW
DECLARE
V_JAEXISTE NUMBER;
BEGIN
  SELECT 1 INTO V_JAEXISTE FROM TRIPULACAO T WHERE T.ID_FUNCIONARIO = :NEW.ID_FUNCIONARIO AND T.DATA = :NEW.DATA;

  IF V_JAEXISTE = 1 THEN
    RAISE_APPLICATION_ERROR (
         num => 1234,
         msg => 'Mensagem de erro');
   END IF;
END;

But really the best alternative would be a UNIQUE KEY composed of these two columns.

Browser other questions tagged oracle pl-sql trigger

You are not signed in. Login or sign up in order to post.