使用触发器的约束

Question

我使用三个表 - 订单、员工和职位。 Orders 有 Employee_no，Employee 表有 Position_no。我想确保订单表中的 Employee_Nos 是销售部门的员工编号。 Sales 中员工的 Position_no 为 3, 4, 5, 6。我使用查询 1（见下文）添加检查约束。但是，它不喜欢子查询。我可以使用查询 2（见下文），它确保 Orders 表中的 Employee_No 在列表中（该列表是销售员工的员工编号）。但是，如果有新员工加入，查询 2 将不起作用。我知道可以使用触发器解决这个问题，但不知道如何解决。如果有人能帮助我，我会很高兴。

Answer 1

在你的情况下我不会使用触发器。它们很难处理，您需要知道如何正确管理它们。

考虑以下解决方案。

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;

我现在没有 Oracle 来测试我的解决方案，但我想您已经明白了。您在 check_sales 中选择了不正确的订单，并添加了一个约束，要求其为空。

Answer 2

啊，类型和子类型。

销售员工是 EMP 的一个子类型。假设 EMP 主键是 EMP_ID。
您可以在 EMP 中创建一个新列 SALES_EMP_ID 和一个检查约束，以便仅在 SALES 中存在员工的地方设置它，并且如果设置，它必须等于 EMP_ID。还有另一个强制唯一性的约束。

然后您可以创建从 ORDERS 到 EMP.SALES_EMP_ID 的引用完整性约束
请参阅以下演示。只有前两次插入 T_EMP 会成功 - 其他测试检查约束。在 T_ORDERS 的插入中，只有第一个（推销员）会成功。

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);

Answer 3

我不经常做触发器，但基本上是这样的。我的语法可能不太正确。

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;