ok.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 38 行

--drop 原有fk
alter table dept_new drop constraint fk_deptno;


--建立父表
CREATE TABLE dept_new
(deptno NUMBER(3) PRIMARY KEY,
dname  varchar2(10));

--将emp表所有deptno 插入dept_new
INSERT INTO dept_new VALUES (30,'DEPT3');
INSERT INTO dept_new VALUES (20,'DEPT2'); 
INSERT INTO dept_new VALUES (10,'DEPT1'); 

--add constranit
--ALTER TABLE emp ADD CONSTRAINT fk_dept FOREIGN KEY(deptno) REFERENCES dept_new(deptno);
ALTER TABLE emp ADD CONSTRAINT fk_dept FOREIGN KEY(deptno) REFERENCES dept_new(deptno);


--create trigger:修改constranit表（dept_new）的primary key.
CREATE OR REPLACE TRIGGER cascade_updates
  AFTER UPDATE  on dept_new
  FOR EACH ROW
   BEGIN
   	
 	UPDATE emp
 	SET    emp.deptno = :new.deptno
	WHERE  emp.deptno = :old.deptno;
   END;

--test
update dept_new set deptno=21 where deptno=20;
--
select * from emp;


 update dept_new set deptno=21 where deptno=20;