Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE TRIGGER t0
- before INSERT ON intervento
- FOR each ROW
- DECLARE
- da_ric NUMBER(3,0);
- eta_paz NUMBER(3,0);
- sa_op intervento.sala_op%TYPE;
- wrong_type EXCEPTION;
- reg_early_op EXCEPTION;
- elderly_early_op EXCEPTION;
- BEGIN
- SELECT (data_ric-data_e_ora),sala_op,FLOOR((SYSDATE-DATA_NASCITA)/365) INTO da_ric,sa_op,eta_paz
- FROM persona NATURAL join paziente join intervento
- ON cf_paz=cf
- WHERE cf_paz=:NEW.cf_paz
- AND sala_op IN ('SALA UNO','SALA DUE');
- IF (:NEW.sala_op='SALA UNO' AND :NEW.tipo<>'LASER') OR (:NEW.sala_op='SALA DUE' AND :NEW.tipo<>'CHIRURGIA GENERALE')
- THEN RAISE wrong_type;
- END IF;
- IF (da_ric<7 AND sa_op='SALA UNO' AND eta_paz <60) OR (da_ric<14 AND sa_op='SALA DUE' AND eta_paz<60)
- THEN RAISE reg_early_op;
- END IF;
- IF (da_ric<12 AND sa_op='SALA UNO' AND eta_paz >=60) OR (da_ric<18 AND sa_op='SALA DUE' AND eta_paz>=60)
- THEN RAISE elderly_early_op;
- END IF;
- EXCEPTION
- WHEN wrong_type THEN raise_application_error(-20001,'Tipo sbagliato');
- WHEN reg_early_op THEN raise_application_error(-20002,'Ricovero troppo recente per operare');
- WHEN elderly_early_op THEN raise_application_error(-20003,'Ricevero troppo recente per un ultra-sessantenne');
- END;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement