华南农业大学数据库系统概念实验报告七

^`

【练习4】建立级联删除触发器CASCADE_DELETE，当删除部门时，级联删除EMP表的雇员记录。 CREATE TRIGGER CASCADE_DELETE AFTER DELETE ON DEPT FOR EACH ROW BEGIN DELETE FROM EMP WHERE EMP.DEPTNO=:OLD.DEPTNO; END; ^`

DELETE FROM DEPT WHERE DEPTNO = 11; COMMIT; SELECT * FROM EMP; ^`

SELECT * FROM DEPT; 【训练5】 CREATE OR REPLACE TRIGGER INITCAP BEFORE INSERT ON EMP FOR EACH ROW BEGIN :new.ename:=INITCAP(:new.ename); END; ^`

INSERT INTO emp(empno,ename,job,sal) VALUES(1000,'BILL','CLERK',1500); SELECT ename,job,sal FROM emp WHERE empno=1000; ^`

【练习5】限定一次对雇员的工资修改不超过原工资的10%。 CREATE OR REPLACE TRIGGER CHECK_SALARY BEFORE UPDATE OF SAL ON EMP FOR EACH ROW BEGIN IF :new.SAL >= :old.SAL*1.1 THEN :new.SAL:= :old.SAL; ELSIF :new.SAL <= :old.SAL*0.9 THEN :new.SAL:= :old.SAL; END IF; END;