I need to make a trigger that will prevent the same employee from joining 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 there already exists in the same row of the table Tripulação o :new.id_Funcionário and :new.data .
The 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.