sql - Constraint using a Trigger -

i using 3 tables - orders, employee , position. orders has employee_no, , employee table has position_no. want make sure employee_nos in order table numbers of employee in sales. position_no of employees in sales 3, 4, 5, 6. used query 1 (see below) add check constraint. however, doesn't subquery. use query 2 (see below) makes sure employee_no in orders table in list (the list employee nos of employees in sales). however, query 2 wouldn't work if new employee joins. know solved using trigger, not sure how. glad if me out.

i don't triggers this. might have syntax not quite right.

create or replace trigger check_order_employee_no   on insert orders   before each row       match_count  integer;   begin     select count(*)       match_count       employee       employee_no = :new.employee_no         , position in (3,4,5,6);     if match_count = 0       raise_application_error( -20000, 'employee # order must sales employee' );     end if;   end check_order_employee_no;