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

the rdv table looks like this :

the daysoff table looks like this :

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;

CodePudding user response：

You can create a temporary table, insert your values into the temporary table and then poupolate rdv form the temporary table using a not exists condition on the dayoff table.

CREATE GLOBAL TEMPORARY TABLE tmp_rdv_insert(
  temps_rdv date,
  etat_rdv varchar(255),
  ID_RDV int
 );

INSERT INTO tmp_rdv_insert(temps_rdv, etat_rdv, ID_RDV)
VALUES (TO_DATE('2023-06-12','YYYY-MM-DD'), 'test', 5);

INSERT INTO rdv
select * from tmp_rdv_insert tmp
where not exists (select 1 from daysoff where DATE_OFF = tmp.temps_rdv);

SQL Fiddle here

CodePudding user response：

Use a MERGE. Then handle WHEN NOT MATCHED to insert or update. Triggers are a terrible way to approach this. Worse case scenario you end up with a mutating trigger and everything goes ka-boom. If you don't want to rely on SQL, you can use PL/SQL. If all else fails, you can use a transient table (for example a temporary table for the current session) to check the values you need (or don't need). Depending on what you want to achieve, I recommend you take a look at MERGE first. It might just be what you need.