question

CREATE OR REPLACE TRIGGER BILL_TRG BEFORE INSERT ON billFOR EACH ROW DECLAREd_id VARCHAR2(20);
doc_id VARCHAR2(20);
R_ID VARCHAR2(20); 
p_name varchar2(20); 
BEGIN
IF :NEW.BILL_NO IS NULL THEN     
       SELECT BILL_NO_SEQ.NEXTVAL INTO :NEW.BILL_NO FROM DUAL;
END IF;
 IF :NEW.BILL_DATE IS NULL THEN       
       UPDATE BILL set BILL_DATE=TO_CHAR(SYSDATE); 
END IF; 
IF :NEW.p_name IS NULL THEN     
       SELECT p_name INTO :NEW.P_NAME FROM PATIENT WHERE P_ID=:NEW.P_ID;
END IF;
 IF :NEW.P_GENDER IS NULL THEN     
      SELECT P_GENDER INTO :NEW.P_GENDER FROM PATIENT WHERE P_ID=:NEW.P_ID;
END IF;
 IF :NEW.P_ADDRESS IS NULL THEN     
      SELECT P_ADDRESS INTO :NEW.P_ADDRESS FROM PATIENT WHERE P_ID=:NEW.P_ID;
END IF;
 IF :NEW.D_NAME IS NULL THEN
     SELECT D_ID INTO DOC_ID FROM PATIENT WHERE P_ID=:NEW.P_ID;
     SELECT D_NAME INTO :NEW.D_NAME FROM DOCTOR WHERE D_ID = DOC_ID;
END IF;
 IF :NEW.DATE_ADMISSION IS NULL THEN
     SELECT DATE_ADMISSION INTO :NEW.DATE_ADMISSION FROM PATIENT WHERE P_ID=:NEW.P_ID;
END IF
; IF :NEW.days_admitted IS NULL THEN
    UPDATE BILL SET DAYS_ADMITTED=abs(to_date(date_admission)-to_date(date_discharge));
END IF;
 IF :NEW.room_charges IS NULL THEN
      SELECT room_id INTO R_ID FROM PATIENT WHERE P_ID=:NEW.P_ID;
      SELECT room_charges INTO :NEW.room_charges FROM room WHERE room_id=R_ID;
      UPDATE BILL SET ROOM_CHARGES=ROOM_CHARGES*DAYS_ADMITTED;
END IF;
 IF :NEW.total_amount IS NULL THEN
    UPDATE BILL SET TOTAL_AMOUNT=ROOM_CHARGES+PATHOLOGY_FEES+D_FEES+MISCELLANEOUS;
END IF;
END;
/