举例
删除dept其中的一个部门,顺带删除员工表emp中这些部门的员工
dept 表
CREATE TABLE SCOTT.DEPT ( DEPTNO NUMBER(2), DNAME VARCHAR2(14 BYTE), LOC VARCHAR2(13 BYTE) ) ALTER TABLE SCOTT.DEPT ADD ( CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO) USING INDEX SCOTT.PK_DEPT);
emp表触发器为CREATE TABLE SCOTT.EMP ( EMPNO NUMBER(4), ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ) ALTER TABLE SCOTT.EMP ADD ( CONSTRAINT EMP_PK PRIMARY KEY (EMPNO) USING INDEX SCOTT.EMP_PK); ALTER TABLE SCOTT.EMP ADD ( CONSTRAINT FK_IDWA FOREIGN KEY (DEPTNO) REFERENCES SCOTT.DEPT (DEPTNO));
然后使用sql 删除DROP TRIGGER SCOTT.DEL_DEPTID; CREATE OR REPLACE TRIGGER SCOTT.del_deptid after delete ON SCOTT.DEPT for each row begin delete emp where empno=:old.deptno; end; /
这样删除部门即可删除部门里的员工delete from dept where deptno=10