1、华南农业大学大数据库系统概念实验报告材料七数据库系统实验报告七学号实验时间2014-12-31实验名称触发器实验学时2准备材料1. SQL Plus命令手册2. PL/SQL用户手册3. 实验教材中实验七扩展实验1.完成触发器的定义、执行与管理操作 (此部分容不要求在实验室完成，不用写入实验报告。)实验环境Oracle 9i(及以上版本)服务器SQL Plus/ SQL Plus work sheet客户端实验目的1 熟悉PL/SQL语法2. 利用PL/SQL编程访问数据库实验容及步骤1.完成实验指导书实验七所有实验容【训练1】CREATE TABLE logs( LOG_ID NUMBER(
2、10) PRIMARY KEY, LOG_TABLE VARCHAR2(10) NOT NULL, LOG_DML VARCHAR2(10), LOG_KEY_ID NUMBER(10), LOG_DATE DATE, LOG_USER VARCHAR2(15) );CREATE SEQUENCE logs_id_squ INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;CREATE OR REPLACE TRIGGER DML_LOG BEFORE -触发时间为操作前 DELETE OR INSERT OR UPDATE
3、 - 由三种事件触发 ON emp FOR EACH ROW - 行级触发器 BEGINIF INSERTING THEN INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,EMP,INSERT,:new.empno,SYSDATE,USER); ELSIF DELETING THEN INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,EMP,DELETE,:old.empno,SYSDATE,USER); ELSE INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,EMP,UPD
4、ATE,:new.empno,SYSDATE,USER); END IF; END;INSERT INTO emp(empno,ename,job,sal) VALUES(8001,MARY,CLERK,1000); COMMIT;SELECT * FROM LOGS;【练习1】修改、删除刚刚插入的雇员记录，提交后检查LOGS表的结果。update emp set sal = sal*5where ename = MARY;COMMIT;DELETE FROM emp WHERE empno = 8001;COMMIT;SELECT * FROM LOGS;【练习2】为DEPT表创建同样的触发
5、器，使用LOGS表进行记录，并检验结果。CREATE OR REPLACE TRIGGER DEPT_LOG BEFORE -触发时间为操作前 DELETE OR INSERT OR UPDATE - 由三种事件触发 ON dept FOR EACH ROW - 行级触发器 BEGINIF INSERTING THEN INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,DEPT,INSERT,:new.deptno,SYSDATE,USER); ELSIF DELETING THEN INSERT INTO logs VALUES(logs_id_squ.
6、NEXTVAL,DEPT,DELETE,:old.deptno,SYSDATE,USER); ELSE INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,DEPT,UPDATE,:new.deptno,SYSDATE,USER); END IF; END;INSERT INTO dept(deptno,dname,loc) VALUES(50,JIAN,GUANGZHOU); COMMIT;SELECT * FROM LOGS;【训练2】CREATE TABLE logerr( NUM NUMBER(10) NOT NULL, MESSAGE VARCHA
7、R2(50) NOT NULL );CREATE OR REPLACE TRIGGER log_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.job=CLERK AND (ABS(new.sal-old.sal)200) DECLARE v_no NUMBER; BEGIN SELECT COUNT(*) INTO v_no FROM logerr; INSERT INTO logerr VALUES(v_no+1,雇员|:new.ename|的原工资：|:old.sal|新工资：|:new.sal); END;UPDATE em
8、p SET sal=sal+550 WHERE empno=7788; UPDATE emp SET sal=sal+500 WHERE empno=7369; UPDATE emp SET sal=sal+50 WHERE empno=7876; COMMIT;SELECT * FROM logerr;【训练3】CREATE OR REPLACE TRIGGER CHECK_SAL BEFORE UPDATE ON emp FOR EACH ROW BEGINIF :new.job=CLERK AND (:new.sal2000) THEN RAISE_APPLICATION_ERROR(-
9、20001, 工资修改超出围,操作取消！); END IF; END;UPDATE emp SET sal=800 WHERE empno=7876; UPDATE emp SET sal=450 WHERE empno=7876; COMMIT;SELECT empno,ename,job,sal FROM emp WHERE empno=7876;【练习3】限定对emp表的修改，只能修改部门10的雇员工资。CREATE OR REPLACE TRIGGER CHECK_NO BEFORE UPDATE ON emp FOR EACH ROW BEGINIF :new.DEPTNO10 TH
10、EN RAISE_APPLICATION_ERROR(-20001, 修改的部门不符,操作取消！); END IF; END;UPDATE emp SET sal=8000 WHERE deptno=20;UPDATE emp SET sal=6789 WHERE deptno=30;UPDATE emp SET sal=888 WHERE deptno=10;select * from emp;【训练4】CREATE TRIGGER CASCADE_UPDATEAFTER UPDATE OF deptnoON DEPTFOR EACH ROWBEGIN UPDATE EMP SET EMP.
11、DEPTNO=:NEW.DEPTNO WHERE EMP.DEPTNO=:OLD.DEPTNO; END;UPDATE dept SET deptno=11 WHERE deptno=10; COMMIT;SELECT empno,ename,deptno FROM emp;【练习4】建立级联删除触发器CASCADE_DELETE，当删除部门时，级联删除EMP表的雇员记录。CREATE TRIGGER CASCADE_DELETEAFTER DELETE ON DEPT FOR EACH ROWBEGIN DELETE FROM EMP WHERE EMP.DEPTNO=:OLD.DEPTNO
12、; END;DELETE FROM DEPT WHERE DEPTNO = 11;COMMIT;SELECT * FROM EMP;SELECT * FROM DEPT;【训练5】CREATE OR REPLACE TRIGGER INITCAPBEFORE INSERT ON EMPFOR EACH ROWBEGIN :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 e
13、mpno=1000;【练习5】限定一次对雇员的工资修改不超过原工资的10%。CREATE OR REPLACE TRIGGER CHECK_SALARYBEFOREUPDATE OF SAL ON EMPFOR EACH ROWBEGIN 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;SELECT * FROM EMP WHERE EMPNO = 7876;UPDATE EMP SET SAL = SA
14、L*1.08 WHERE EMPNO = 7876;COMMIT;SELECT * FROM EMP WHERE EMPNO = 7876;7.2.4【训练1】CREATE OR REPLACE TRIGGER CHECK_TIMEBEFOREUPDATE OR INSERT OR DELETE ON EMPBEGIN IF (TO_CHAR(SYSDATE,DY) IN (SAT,SUN) OR TO_CHAR(SYSDATE,HH24)=17 THENRAISE_APPLICATION_ERROR(-20500,非法时间修改表错误！); END IF; END;UPDATE EMP SET
15、 SAL=3000 WHERE EMPNO=7369;【练习1】设计一个语句级触发器，限定只能对数据库进行修改操作，不能对数据库进行插入和删除操作。在需要进行插入和删除时，将触发器设置为无效状态，完成后重新设置为生效状态。CREATE OR REPLACE TRIGGER onlyupdateBEFOREUPDATE OR INSERT OR DELETE ON EMPBEGIN IF deleting or inserting thenRAISE_APPLICATION_ERROR(-20500,非法操作！); END IF; END;7.3【训练1】CREATE OR REPLACE TR
16、IGGER NODROP_EMPBEFOREDROP ON SCHEMA BEGINIF Sys.Dictionary_obj_name=EMP THENRAISE_APPLICATION_ERROR(-20005,错误信息：不能删除emp表！); END IF; END;DROP TABLE emp;7.4【训练1】CREATE VIEW emp_name AS SELECT ename FROM emp;CREATE OR REPLACE TRIGGER change_name INSTEAD OF INSERT ON emp_name DECLAREV_EMPNO NUMBER(4);
17、BEGIN SELECT MAX(EMPNO)+1 INTO V_EMPNO FROM EMP; INSERT INTO emp(empno,ename) VALUES(V_EMPNO,:new.ename); END;INSERT INTO emp_name VALUES(BROWN); COMMIT;【训练2】CREATE OR REPLACE TRIGGER delete_from_ename INSTEAD OF DELETE ON emp_name BEGIN RAISE_APPLICATION_ERROR(-20006,错误信息：不能在视图中删除emp表的雇员！); END;实验七创建触发器，进行表的同步复制CREATE OR REPLACE TRIGGER copytimebefore INSert OF DELETE or UPDATE ON cc; BEGIN if inserting then insert into copynew values(:new id); elsif deleting then delete copynew where id=:old.id; else update copynew set id:=:new id where id=:old id; end if; END;出现问题解决方案（列出遇到的问题及其解决方法）
