Trigger to prevent repeated data on ORACLE

Question

Opa personal I am trying to create a Trigger to prevent the entry of repeated data in Oracle but am not succeeding.

You cannot use Constraint Unique, because I will have repeated data, because it is a table of logs that changes is the status that 0 is processing, 1 processed and 2 attempt... already created exactly to prevent duplicities, but somehow the user manages to pass... to understand when starting the process launches the status 0 and then arrow to 1, however if when the status is 0 and someone tries to create the same process for merchandise the system would not allow launching the status 2, then there can be several attempts registering the log with status 2, that I pick, start and end time of the process and user.

CREATE OR REPLACE TRIGGER TR_APONTAMENTO_SERVICO_DUPLICIDADE_BLOCK
BEFORE INSERT ON EVENTO_APONTAMENTO_SERVICO FOR EACH ROW WHEN (NEW.EAPS_ID IS NULL)
        BEGIN
            IF 
                (SELECT COUNT(*) FROM EVENTO_APONTAMENTO_SERVICO 
                        WHERE EAPS_MERC_ID = NEW.EAPS_MERC_ID 
                            AND EAPS_ESCA_ID = NEW.EAPS_ESCA_ID 
                            AND EAPS_STATUS = 1) > 0
                            
                THEN 
            
            INSERT INTO EVENTO_APONTAMENTO_SERVICO
                (EAPS_MERC_ID, EAPS_ESCA_ID, EAPS_USUA_ID, EAPS_DTHR_INICIO, EAPS_DTHR_FIM, EAPS_OBSERVACAO, EAPS_STATUS)
                VALUES
                (NEW.EAPS_MERC_ID, NEW.EAPS_ESCA_ID ,NEW.EAPS_USUA_ID, NEW.EAPS_DTHR_INICIO, NEW.EAPS_DTHR_FIM, NEW.EAPS_OBSERVACAO, 2);
            
            END IF;

END;

This Internet I can remove and treat it in C#, but I wanted to take an exception to treat it

Answer 1

I managed to solve, it follows below

CREATE OR REPLACE TRIGGER R_APONTAMENTO_SERVICO_DUPLICIDADE_BLOCK
BEFORE INSERT ON EVENTO_APONTAMENTO_SERVICO  FOR EACH ROW

DECLARE 
    Evento varchar2(50);

    CURSOR V_AREA IS SELECT EAPS_ID FROM EVENTO_APONTAMENTO_SERVICO 
                     WHERE EAPS_MERC_ID = :NEW.EAPS_MERC_ID 
                     AND EAPS_ESCA_ID = :NEW.EAPS_ESCA_ID 
                     AND EAPS_STATUS = 0;
BEGIN
    OPEN V_AREA;
    FETCH V_AREA INTO Evento;
         
    IF V_AREA%FOUND 
        THEN 
            IF :NEW.EAPS_STATUS = 0 
            THEN
            RAISE_APPLICATION_ERROR(-20500,'Mercadoria em processamento.'); 
            END IF;
    END IF;

CLOSE V_AREA;

END;