check if value exist in another table before insert SQL

Question

I have table: " daysoff " and table: " rdv "

the rdv table looks like this :

temps_rdv	etat_rdv	ID_RDV
12-10-2022	reservee	2

the daysoff table looks like this :

ID	DATE_OFF	REASON
2	12-06-2023	conge

i want to insert values in rdv table, but before inserting any thing i need to check first if " temps_rdv " doesn't exist in the daysoff table

example: I can not add a rdv with temps_rdv = 12-06-2023

I tried a trigger, but it doesn't seem to work

  CREATE OR REPLACE TRIGGER TRIGGER1 
    BEFORE INSERT ON rdv
    FOR EACH ROW
        BEGIN
            IF EXISTS (
                select daysoff.date_off
                From Available daysoff  -- CHANGED THE ALIAS TO A
                where (NEW.temps_rdv = daysoff.date_off)
            ) THEN 
               CALL:='Insert not allowed';
    
            END IF;
    END;

I also tried this :

CREATE TRIGGER TR2
BEFORE INSERT ON RDV
FOR EACH ROW 
DECLARE found_it INT;
--     IF (NEW.temps_rdv != "01-01-23" AND NEW.temps_rdv != "02-01-23" AND NEW.temps_rdv != "03-01-23"  ) THEN
        
BEGIN
     SELECT COUNT(1) INTO found_it FROM daysoff
        WHERE NEW.temps_rdv != "01-01-23" AND NEW.temps_rdv != "02-01-23" AND NEW.temps_rdv != "03-01-23";
        IF (found_it = 0) THEN
        INSERT INTO rdv (temps_rdv, etat_rdv, id_rdv, patient_id_p) VALUES (NEW.temps_rdv, NEW.etat_rdv, NEW.id_rdv, NEW.patient_id_p);
        END IF;
    END IF;
END;

but none of these worked

Answer 1

Here's one option:

SQL> create or replace trigger trg_bi_rdv
  2    before insert on rdv
  3    for each row
  4  declare
  5    l_cnt number;
  6  begin
  7    select count(*)
  8      into l_cnt
  9      from daysoff d
 10      where d.id = :new.id_rdv
 11        and d.date_off = :new.temps_rdv;
 12
 13    if l_cnt > 0 then
 14       raise_application_error(-20000, 'Insert not allowed');
 15    end if;
 16  end;
 17  /

Trigger created.

Testing:

SQL> insert into rdv (temps_rdv, etat_rdv, id_rdv) values
  2    (date '2023-01-25', 'test', 1);

1 row created.

SQL> insert into rdv (temps_rdv, etat_rdv, id_rdv) values
  2    (date '2023-01-25', 'test', 2);

1 row created.

SQL> insert into rdv (temps_rdv, etat_rdv, id_rdv) values
  2    (date '2023-06-12', 'test', 2);
insert into rdv (temps_rdv, etat_rdv, id_rdv) values
            *
ERROR at line 1:
ORA-20000: Insert not allowed
ORA-06512: at "SCOTT.TRG_BI_RDV", line 11
ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_RDV'


SQL>