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

《数据库系统》实验报告七学号姓名实验时间2014-12-31实验名称触发器实验学时2准备材料1.SQLPlus命令手册2.PL/SQL用户手册3.实验教材中实验七扩展实验1.完成触发器的定义、执行与管理操作(此部分内容不要求在实验室完成，不用写入实验报告。)实验环境Oracle9i(及以上版本)服务器SQLPlus/SQLPlusworksheet客户端实验目的1熟悉PL/SQL语法2.利用PL/SQL编程访问数据库实验内容及步骤1.完成实验指导书实验七所有实验内容【训练1】CREATETABLElogs(LOG_IDNUMBER(10)PRIMARYKEY,LOG_TABLEVARCHAR2(10)NOTNULL,LOG_DMLVARCHAR2(10),LOG_KEY_IDNUMBER(10),LOG_DATEDATE,LOG_USERVARCHAR2(15));CREATESEQUENCElogs_id_squINCREMENTBY1STARTWITH1MAXVALUE9999999NOCYCLENOCACHE;CREATEORREPLACETRIGGERDML_LOGBEFORE--触发时间为操作前DELETEORINSERTORUPDATE--由三种事件触发ONempFOREACHROW--行级触发器BEGINIFINSERTINGTHENINSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'EMP','INSERT',:new.empno,SYSDATE,USER);ELSIFDELETINGTHENINSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'EMP','DELETE',:old.empno,SYSDATE,USER);ELSEINSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'EMP','UPDATE',:new.empno,SYSDATE,USER);ENDIF;END;INSERTINTOemp(empno,ename,job,sal)VALUES(8001,'MARY','CLERK',1000);COMMIT;SELECT*FROMLOGS;【练习1】修改、删除刚刚插入的雇员记录，提交后检查LOGS表的结果。updateempsetsal=sal*5whereename='MARY';COMMIT;DELETEFROMempWHEREempno=8001;COMMIT;SELECT*FROMLOGS;【练习2】为DEPT表创建同样的触发器，使用LOGS表进行记录，并检验结果。CREATEORREPLACETRIGGERDEPT_LOGBEFORE--触发时间为操作前DELETEORINSERTORUPDATE--由三种事件触发ONdeptFOREACHROW--行级触发器BEGINIFINSERTINGTHENINSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'DEPT','INSERT',:new.deptno,SYSDATE,USER);ELSIFDELETINGTHENINSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'DEPT','DELETE',:old.deptno,SYSDATE,USER);ELSEINSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'DEPT','UPDATE',:new.deptno,SYSDATE,USER);ENDIF;END;INSERTINTOdept(deptno,dname,loc)VALUES(50,'JIAN','GUANGZHOU');COMMIT;SELECT*FROMLOGS;【训练2】CREATETABLElogerr(NUMNUMBER(10)NOTNULL,MESSAGEVARCHAR2(50)NOTNULL);CREATEORREPLACETRIGGERlog_salBEFOREUPDATEOFsalONempFOREACHROWWHEN(new.job='CLERK'AND(ABS(new.sal-old.sal)200))DECLAREv_noNUMBER;BEGINSELECTCOUNT(*)INTOv_noFROMlogerr;INSERTINTOlogerrVALUES(v_no+1,'雇员'||:new.ename||'的原工资：'||:old.sal||'新工资：'||:new.sal);END;UPDATEempSETsal=sal+550WHEREempno=7788;UPDATEempSETsal=sal+500WHEREempno=7369;UPDATEempSETsal=sal+50WHEREempno=7876;COMMIT;SELECT*FROMlogerr;【训练3】CREATEORREPLACETRIGGERCHECK_SALBEFOREUPDATEONempFOREACHROWBEGINIF:new.job='CLERK'AND(:new.sal500OR:new.sal2000)THENRAISE_APPLICATION_ERROR(-20001,'工资修改超出范围,操作取消！');ENDIF;END;UPDATEempSETsal=800WHEREempno=7876;UPDATEempSETsal=450WHEREempno=7876;COMMIT;SELECTempno,ename,job,salFROMempWHEREempno=7876;【练习3】限定对emp表的修改，只能修改部门10的雇员工资。CREATEORREPLACETRIGGERCHECK_NOBEFOREUPDATEONempFOREACHROWBEGINIF:new.DEPTNO10THENRAISE_APPLICATION_ERROR(-20001,'修改的部门不符,操作取消！');ENDIF;END;UPDATEempSETsal=8000WHEREdeptno=20;UPDATEempSETsal=6789WHEREdeptno=30;UPDATEempSETsal=888WHEREdeptno=10;select*fromemp;【训练4】CREATETRIGGERCASCADE_UPDATEAFTERUPDATEOFdeptnoONDEPTFOREACHROWBEGINUPDATEEMPSETEMP.DEPTNO=:NEW.DEPTNOWHEREEMP.DEPTNO=:OLD.DEPTNO;END;UPDATEdeptSETdeptno=11WHEREdeptno=10;COMMIT;SELECTempno,ename,deptnoFROMemp;【练习4】建立级联删除触发器CASCADE_DELETE，当删除部门时，级联删除EMP表的雇员记录。CREATETRIGGERCASCADE_DELETEAFTERDELETEONDEPTFOREACHROWBEGINDELETEFROMEMPWHEREEMP.DEPTNO=:OLD.DEPTNO;END;DELETEFROMDEPTWHEREDEPTNO=11;COMMIT;SELECT*FROMEMP;SELECT*FROMDEPT;【训练5】CREATEORREPLACETRIGGERINITCAPBEFOREINSERTONEMPFOREACHROWBEGIN:new.ename:=INITCAP(:new.ename);END;INSERTINTOemp(empno,ename,job,sal)VALUES(1000,'BILL','CLERK',1500);SELECTename,job,salFROMempWHEREempno=1000;【练习5】限定一次对雇员的工资修改不超过原工资的10%。CREATEORREPLACETRIGGERCHECK_SALARYBEFOREUPDATEOFSALONEMPFOREACHROWBEGINIF:new.SAL=:old.SAL*1.1THEN:new.SAL:=:old.SAL;ELSIF:new.SAL=:old.SAL*0.9THEN:new.SAL:=:old.SAL;ENDIF;END;SELECT*FROMEMPWHEREEMPNO=7876;UPDATEEMPSETSAL=SAL*1.08WHEREEMPNO=7876;COMMIT;SELECT*FROMEMPWHEREEMPNO=7876;7.2.4【训练1】CREATEORREPLACETRIGGERCHECK_TIMEBEFOREUPDATEORINSERTORDELETEONEMPBEGINIF(TO_CHAR(SYSDATE,'DY')IN('SAT','SUN'))ORTO_CHAR(SYSDATE,'HH24')'08'ORTO_CHAR(SYSDATE,'HH24')='17'THENRAISE_APPLICATION_ERROR(-20500,'非法时间修改表错误！');ENDIF;END;UPDATEEMPSETSAL=3000WHEREEMPNO=7369;【练习1】设计一个语句级触发器，限定只能对数据库进行修改操作，不能对数据库进行插入和删除操作。在需要进行插入和删除时，将触发器设置为无效状态，完成后重新设置为生效状态。CREATEORREPLACETRIGGERonlyupdateBEFOREUPDATEORINSERTORDELETEONEMPBEGINIFdeletingorinsertingthenRAISE_APPLICATION_ERROR(-20500,'非法操作！');ENDIF;END;7.3【训练1】CREATEORREPLACETRIGGERNODROP_EMPBEFOREDROPONSCHEMABEGINIFSys.Dictionary_obj_name='EMP'THENRAISE_APPLICATION_ERROR(-20005,'错误信息：不能删除emp表！');ENDIF;END;DROPTABLEemp;7.4【训练1】CREATEVIEWemp_nameASSELECTenameFROMemp;CREATEORREPLACETRIGGERchange_nameINSTEADOFINSERTONemp_nameDECLAREV_EMPNONUMBER(4);BEGINSELECTMAX(EMPNO)+1INTOV_EMPNOFROMEMP;INSERTINTOemp(empno,ename)VALUES(V_EMPNO,:new.ename);END;INSERTINTOemp_nameVALUES('BROWN');COMMIT;【训练2】CREATEORREPLACETRIGGERdelete_from_enameINSTEADOFDELETEONemp_nameBEGINRAISE_APPLICATION_ERROR(-20006,'错误信息：不能在视图中删除emp表的雇员！');END;实验七创建触发器，进行表的同步复制CREATEORREPLACETRIGGERcopytimebeforeINSertOFDELETEorUPDATEONcc;BEGINifinsertingtheninsertintocopynewvalues(:newid);elsifdeletingthendeletecopynewwhereid=:old.id;elseupdatecopynewsetid:=:newidwhereid=:oldid;endif;END;出现问题解决方案（列出遇到的问题及其解决方法）