Constraint using a Trigger

I am using three tables - Orders, Employee and Position. Orders has Employee_no, and Employee table has Position_no. I want to make sure that the Employee_Nos in Order table are the numbers of those employee who are in Sales. The Position_no of employees in Sales are 3, 4, 5, 6. I used Query 1 (see below) to add 开发者_运维问答a check constraint. However, it doesn't like the subquery. I could use Query 2 (see below) which makes sure that the Employee_No in Orders table is in the list (the list is the employee nos of employees who are in sales). However, Query 2 wouldn't work if a new employee joins. I know this could be solved using a trigger, but not sure how. Would be glad if somebody could help me out.

I wouldn't use triggers in your case. They are difficult to deal with and you need to know how to manage them correctly.

Consider the following solution.

create materialized view check_sales
refresh complete on commit as
select 1 dummy
from ORDERS where 
EMPLOYEE_NO IS NOT IN ( SELECT EMPLOYEE_NO FROM EMPLOYEE WHERE POSITION IN (3,4,5,6))

alter table check_sales
add constraint check_sales_empty
check (1=0) deferrable;

I don't have the Oracle now to test my solution, but I think you got the idea. You select incorrect orders into check_sales and add a constraint that it should be empty.

Ah, types and subtypes.

A SALES employee is a subtype of EMP. Assume the EMP primary key is EMP_ID. You can create a new column SALES_EMP_ID in EMP and a check constraint so that it is only set where there employee is in SALES and that, if set, it must be equal to EMP_ID. And another constraint to enforce uniqueness.

Then you can create a referential integrity constraint from ORDERS to EMP.SALES_EMP_ID See the following demo. Only the first two inserts into T_EMP will succeed - the others test the check constraints. Of the inserts into T_ORDERS, only the first (salesman) will succeed.

drop table t_emp purge; 

CREATE TABLE t_emp 
  (emp_id number primary key, emp_name varchar2(20),
  dept_name varchar2(10),
  sales_emp_id number,
  constraint sales_emp_id_sales_ck check 
      ((sales_emp_id is null and dept_name != 'SALES') or 
       (dept_name = 'SALES' and sales_emp_id = emp_id and sales_emp_id is not null)),
  constraint sales_emp_id_uk unique (sales_emp_id));

insert into t_emp values (1,'Alan','SALES',1);
insert into t_emp values (2,'Bill','ACCOUNTS',null);
insert into t_emp values (3,'Chuck','ACCOUNTS',3);
insert into t_emp values (4,'Dan','SALES',null);
insert into t_emp values (5,'Ellen','SALES',6);

select * from t_emp;

create table t_orders
  (ord_id number primary key,
  salesman number,
  constraint salesman_fk foreign key (salesman) references t_emp(sales_emp_id));

insert into t_orders values (1,1);
insert into t_orders values (2,2);

I don't do triggers often but it would basically be like this. I might have the syntax not quite right.

CREATE OR REPLACE TRIGGER check_order_employee_no
  ON INSERT INTO orders
  BEFORE EACH ROW
  AS
    match_count  INTEGER;
  BEGIN
    SELECT COUNT(*)
      INTO match_count
      FROM employee
      WHERE employee_no = :new.employee_no
        AND position IN (3,4,5,6);
    IF match_count = 0 THEN
      raise_application_error( -20000, 'Employee # for order must be for a Sales employee' );
    END IF;
  END check_order_employee_no;