CREATE OR REPLACE TRIGGER hunter_select
        BEFORE INSERT ON Contract
        FOR EACH ROW
DECLARE
        cost NUMBER;
        d_line DATE;
        prepay NUMBER;
        diff NUMBER;
        skilled_hunters NUMBER;
        cond_id NUMBER;
        armed_hunters NUMBER;
        live_hunters NUMBER;
        free_hunters NUMBER;
        no_money_err EXCEPTION;
        death_hunter EXCEPTION;
        working_hunter EXCEPTION;
        unarmed_hunter EXCEPTION;
        unskilled_hunter EXCEPTION;
        too_late EXCEPTION;
BEGIN
        SELECT SUM(base_cost) INTO cost FROM task_base
                WHERE id IN (SELECT task_id FROM tasks_of_orders
                        WHERE order_id = :NEW.order_id);
        SELECT deadline INTO d_line FROM "Order"
                WHERE id = :NEW.order_id;
        SELECT prepayment INTO prepay FROM "Order"
                WHERE id = :NEW.order_id;
        --prepayment
        SELECT difficulty INTO diff FROM "Order"
                WHERE id = :NEW.order_id;
        --difficulty
        SELECT MAX(conditions_id) INTO cond_id FROM tasks_of_orders
                WHERE order_id  = :NEW.order_id;
        --conditions
        SELECT COUNT(id) INTO live_hunters FROM hunters
                WHERE status = 'жив' AND id = :NEW.hunter_id;
        --0?exc:ok;
        SELECT COUNT(hunter_id) INTO free_hunters FROM contract
                WHERE id = :NEW.hunter_id AND status = 'выполняется';
        --!0?exc:ok
        SELECT COUNT(hunter_id) INTO skilled_hunters FROM ratings
                WHERE conditions_id = cond_id AND hunter_id = :NEW.hunter_id
                AND rating > diff;
         --rating>difficulty?ok:exc
         SELECT COUNT(hunter_id) INTO armed_hunters FROM hunter_equipment
                WHERE hunter_id = :NEW.hunter_id
                AND equipment_id IN (SELECT id FROM equipment
                                        WHERE conditions_id = cond_id);
        --_armed_hunters>0?ok:exc
        IF d_line < SYSDATE THEN RAISE too_late; END IF;
        IF prepay = 0 THEN RAISE no_money_err; END IF;
        IF live_hunters = 0 THEN RAISE death_hunter; END IF;
        IF free_hunters = 0 THEN RAISE working_hunter; END IF;
        IF skilled_hunters = 0 THEN RAISE unskilled_hunter; END IF;
        IF armed_hunters = 0 THEN RAISE unarmed_hunter; END IF;
        :NEW.final_cost := cost*diff;
        SELECT Contract_seq.NEXTVAL INTO :NEW.id FROM dual;
EXCEPTION
        WHEN unarmed_hunter THEN RAISE_APPLICATION_ERROR(-20571,'Naked!');
        WHEN working_hunter THEN RAISE_APPLICATION_ERROR(-20569,'Busy');
        WHEN death_hunter THEN RAISE_APPLICATION_ERROR(-20568,'(S)He_s dead');
        WHEN too_late THEN RAISE_APPLICATION_ERROR(-20567,'Too late!');
        WHEN no_money_err THEN RAISE_APPLICATION_ERROR(-20566,'No money!');
        WHEN unskilled_hunter THEN RAISE_APPLICATION_ERROR(-20570,'Unskilled');
END hunter_select;
/