3

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

1
  • I removed your SQL Server tag as that represents the product Microsoft SQL Server, and it appears you're using Oracle SQL.
    – J.D.
    Dec 5, 2022 at 21:11

1 Answer 1

0

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.