触发器或check问题，我也不清楚..

Question

我需要从一个表中删除记录，但删除之前需要检查是否最后一个满足某条件的 如：部门下最后一个用户不可删。
因为部门会有树状的层次结构，删除部门用的存储过程，现在想在用户表里添加一个触发器或是check约束能检查一下，哪一个能实现这种功能？
    select count(*) from usertable t where t.deptid = '21'
    如果count为1则不可以删
 已做的工作
1、尝试使用check，但不知道用哪个操作符，而且好像check是对某行的记录做检查的，不知道能不能对某列的记录做统计检查
2、对触发器，before delete触发，但因为对表记录做了改变，上面的sql语句在触发期中执行错误

Answer 1

使用before触发器，在删除之前，根据提供的部门号，对deptid分组，统计人数，若是等于1就不做，大于1就执行删除操作

Answer 2

变异表的触发器 问题：
参考脚本如下：CREATE TABLE usertable 
(
ID INT,
deptid CHAR(2)
);INSERT INTO usertable VALUES(1,'01');
INSERT INTO usertable VALUES(2,'01');
INSERT INTO usertable VALUES(3,'21');
INSERT INTO usertable VALUES(4,'21');
INSERT INTO usertable VALUES(5,'21');
COMMIT;
--------- 1 PACKAGE
CREATE OR REPLACE PACKAGE USER_DEPTID AS
  V_DEPTID USERTABLE.DEPTID%TYPE;
END USER_DEPTID;
/
-------- 2 trigger1 保存当前要删除的deptid
CREATE OR REPLACE TRIGGER TRI_USERTABLE_1
  BEFORE DELETE ON USERTABLE
  FOR EACH ROW
BEGIN
  USER_DEPTID.V_DEPTID := :old.DEPTID;
END;
/
--------- 3  trigger2 判断是否该删除
CREATE OR REPLACE TRIGGER TRI_USERTABLE_2
  AFTER DELETE ON USERTABLE 
DECLARE
  V_COUNT INT;
BEGIN
  DBMS_OUTPUT.put_line(USER_DEPTID.V_DEPTID);
  SELECT COUNT(DEPTID)
    INTO V_COUNT
    FROM USERTABLE
   WHERE DEPTID = USER_DEPTID.V_DEPTID;
 DBMS_OUTPUT.put_line(V_COUNT);
  IF V_COUNT = 0
  THEN
    RAISE_APPLICATION_ERROR(-20000,
                            'You can not delete the last deptid:' ||
                            USER_DEPTID.V_DEPTID);
  END IF;
END;
/
 

Answer 3

----------------------- 测试过程
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
Connected as dc_etl
 
SQL> select * from usertable;
 
                                     ID DEPTID
--------------------------------------- ------
                                      1 01
                                      2 01
                                      3 21
                                      4 21
                                      5 21
 
SQL> delete from usertable where id = 1;
 
1 row deleted
 
SQL> commit;
 
Commit complete
 
SQL> select * from usertable;
 
                                     ID DEPTID
--------------------------------------- ------
                                      2 01
                                      3 21
                                      4 21
                                      5 21
 
SQL> delete from usertable where id = 2;
 
delete from usertable where id = 2
 
ORA-20000: You can not delete the last deptid:01
ORA-06512: 在 "DC_ETL.TRI_USERTABLE_2", line 12
ORA-04088: 触发器 'DC_ETL.TRI_USERTABLE_2' 执行过程中出错
  
 
SQL> select * from usertable;
 
                                     ID DEPTID
--------------------------------------- ------
                                      2 01
                                      3 21
                                      4 21
                                      5 21
 
SQL> delete from usertable where id = 3;commit;
 
delete from usertable where id = 3;commit
 
ORA-00911: 无效字符
 
SQL> delete from usertable where id = 3;
 
1 row deleted
 
SQL> commit;
 
Commit complete
 
SQL> delete from usertable where id = 4;
 
1 row deleted
 
SQL> commit;
 
Commit complete
 
SQL> delete from usertable where id = 5;
 
delete from usertable where id = 5
 
ORA-20000: You can not delete the last deptid:21
ORA-06512: 在 "DC_ETL.TRI_USERTABLE_2", line 12
ORA-04088: 触发器 'DC_ETL.TRI_USERTABLE_2' 执行过程中出错
 
SQL> select * from usertable;
 
                                     ID DEPTID
--------------------------------------- ------
                                      2 01
                                      5 21
 
SQL> 

Answer 4

十分感谢 但我执行的时候在select 那句报错：ORA-04091表发生了变化，触发器/函数不能读，这是怎么回事？我的表里有主键、外键、和一个 unique，有关系么？

Answer 5

SELECT COUNT(DEPTID)
    INTO V_COUNT
    FROM USERTABLE
   WHERE DEPTID = USER_DEPTID.V_DEPTID;
     DBMS_OUTPUT.put_line(V_COUNT);
  IF V_COUNT = 0
这个地方：where 后面的做什么用？不是只判断要删的数据是不是最后一条就可以了吗？直接就是SELECT COUNT(DEPTID)INTO V_COUNT FROM USERTABLE IF V_COUNT = 0，这样不就已经是最后一条了吗

Answer 6

注意suncrafted强调的几个点，第一，变异表的触发器也就是说，触发器里有对表的select操作。这样的操作多于for each row是不允许的，除非是自治事务第二，主要看suncrafted的实现，定义了一个package，里面定义了一个变量定义了两个trigger，第一个是for each row的before，第二个是表级的after。这里的每个代码都是有作用的。仔细研究一下suncrafted的实现吧。如果你的有错，把你的和suncrafted对照一下，你们的实现思路肯定不一样。