create or replace trigger Available_Patient
before insert or update on APPOINTMENT
FOR EACH ROW follows check_schedule
DECLARE
count_app_of_pat NUMBER(10);
Available_Patients BOOLEAN :=FALSE;
ST DATE;
FN DATE;
POINTER DATE;
begin
SELECT Count(*) into count_app_of_pat 
FROM APPOINTMENT 
WHERE :new.policy_number = policy_number AND
data_appointment = :new.data_appointment AND
ABS(to_date(:new.time, 'hh24:mi') - to_date(time, 'hh24:mi'))*24*60<20;

Available_Patients := count_app_of_pat=0;

IF NOT (Available_Patients) THEN 
raise_application_error(-20011,'Пациент в это время на приеме!');
END IF;
END;

create or replace trigger testone
  for update on appointment
  compound trigger
    count_app_of_pat NUMBER(10);
    patient number(10);
    dat date;
    timeapp varchar2(5);
    
     before each row is
     begin
     dat := :new.data_appointment;
     timeapp := :new.time;
     patient := :new.policy_number;
     end before each row;
     
     after statement is
     begin
     SELECT Count(*) into count_app_of_pat  
     FROM APPOINTMENT 
     WHERE patient = policy_number AND
     data_appointment = dat AND
     ABS(to_date(timeapp, 'hh24:mi') - to_date(time, 'hh24:mi'))*24*60<20;

     IF NOT (count_app_of_pat=0) THEN 
     raise_application_error(-20011,'Пациент в это время на приеме!');
     END IF;
     end after statement;

end testone;