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

1、实用文档数据库系统实验报告七学号姓名实验时间2014-12-31实验名称触发器实验学时2准备材料1. SQLPlus命令手册2. PL/SQL用户手册3. 实验教材中实验七扩展实验1.完成触发器的定义、执行与管理操作（此部分内容不要求在实验室完成，不用写入实验报告。）实验环境Oracle9i(及以上版本)服务器SQLPlus/SQLPlusworkshee跳户端实验目的1熟悉PL/SQL语法2.利用PL/SQL编程访问数据库实验内容及步骤1.完成实验指导书实验七所有实验内容【训练1】CREATETABLElogs(LOG_IDNUMBER(10)PRIMARYKEY,LOG_TABLEVARC

2、HAR2(10)NOTNULL,LOG_DMLVARCHAR2(10),LOG_KEY_IDNUMBER(10),LOG_DATEDATE,LOG_USERVARCHAR2(15);标准实用文档二箕EfsonCREATEORREPLACETRIGGERDMLLOGBEFORE触发时间为操作前DELETEORINSERTORUPDATE-由三种事件触发ONempBEGINE如ttaKlMTl-h|酢-Ik'.FOREACHROW-行级触发器F1*3F*0Vr-*I<r4!i-ai口£1党号”修，StWflK"：llW标准CREATESEQUENCElogs_id

3、_squINCREMENTBY1STARTWITH1MAXVALUE9999999NOCYCLENOCACHE;E9EOOEMElogUL"N!HIVAX实用文档IFINSERTINGTHENINSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'EMP','INSERT',:new.empno,SYSDATE,USER);ELSIFDELETINGTHENINSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'EMP','DELETE',:old.empno,SY

4、SDATE,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;?LOGS表的结果。【练习1】修改、删除刚刚插入的雇员记录，提交后检查updateempsetsal=sal*5whereenam

5、e='MARY'COMMIT;标准实用文档DELETEFROMempWHEREempno=8001;COMMIT;SELECT*FROMLOGS;标准实用文档【练习2】为DEPT表创建同样的触发器，使用LOGS表进行记录，并检验结果。CREATEORREPLACETRIGGERDEPT_LOGBEFORE-触发时间为操作前DELETEORINSERTORUPDATE-由三种事件触发ONdeptFOREACHROW-行级触发器BEGINIFINSERTINGTHENINSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'DEPr,'IN

6、SERT',: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,l

7、oc)ALUES(50,'JIAN','GUANGZHOU');COMMIT;SELECT*FROMLOGS;标准实用文档FilHWhiHSnoATI-?【训练2】CREATETABLElogerr(NUMNUMBER(10)NOTNULL,MESSAGEVARCHAR2(50)NOTNULL);CREATEORREPLACETRIGGERlog_salBEFOREUPDATEOFsalONemp标准实用文档FOREACHROWWHEN(new.job='CLERK'AND(ABS(new.sal-old.sal)>200)DECLAREv

8、_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;标准实用文档【训练3】CREATEORREPLACETRIGGERCHE

9、CK_SALBEFOREUPDATEONempFOREACHROWBEGINIF:new.job='CLERK'AND(:new.sal<500OR:new.sal>2000)THENRAISEAPPLICATIONERROR(-20001,'工资修改超出范围，操作取消！);标准实用文档F1!+，*r4il-fRENDIF;END;UPDATEempSETsal=800WHEREempno=7876;UPDATEempSETsal=450WHEREempno=7876;COMMIT;FROM标准实用文档【练习3】限定对emp表的修改，只能修改部门10的雇员工

10、资。CREATEORREPLACETRIGGERCHECK_NOBEFOREUPDATEONempFOREACHROWBEGINIF:new.DEPTNO<>10THENRAISE_APPLICATION_ERROR（-20001,'修改的部门不符，操作取消！）;ENDIF;END;标准实用文档UPDATEempSETsal=8000WHEREdeptno=20;UPDATEempSETsal=6789WHEREdeptno=30;UPDATEempSETsal=888WHEREdeptno=10;kL»ri标准select*fromemp;实用文档,，中Flwf

11、*4掌*皤MtK1F'A9V白，小.HL>FbfiLl-Lib*+*,jdHlshrriK11口dttW*H皿1>51“九一1>、C>>h"-M«A,i<4,，=NRid,胃【训练4】CREATETRIGGERCASCADE_UPDATEAFTERUPDATEOFdeptnoONDEPTFOREACHROWBEGINUPDATEEMPSETEMP.DEPTNO=:NEW.DEPTNOWHEREEMP.DEPTNO=:OLD.DEPTNO;END;标准实用文档UPDATEdeptSETdeptno=11WHEREdeptno=10;

12、COMMIT;£14171Q-JI?缶一一季t'J*1fl.KRK-SELECTempno,ename,deptnoFROMemp;标准实用文档【练习4】建立级联删除触发器CASCADE_DELETE,当删除部门时，级联删除EMP表的雇员记录。CREATETRIGGERCASCADE_DELETEAFTERDELETEONDEPTFOREACHROWBEGINDELETEFROMEMPWHEREEMP.DEPTNO=:OLD.DEPTNO;END;标准实用文档DELETEFROMDEPTWHEREDEPTNO=11;COMMIT;SELECT*FROMEMP;标准实用文档SE

13、LECT*FROMDEPT;y1*«14m*中EEEH吟ZHi1H3"8E£ZUTT白IGurmi.I_|*-ft1【训练5】CREATEORREPLACETRIGGERINITCAPBEFOREINSERTONEMPFOREACHROWBEGIN:new.ename:=INITCAP(:new.ename);END;标准实用文档INSERTINTOemp(empno,ename,job,sal)VALUES(1000,'BILL','CLERK',1500);SELECTename,job,salFROMempWHEREempno

14、=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;

15、COMMIT;SELECT*FROMEMPWHEREEMPNO=7876;31*!：-rumecK3二-it-*.-47.2.4【训练1】CREATEORREPLACETRIGGERCHECK_TIMEBEFORE标准实用文档UPDATEORINSERTORDELETEONEMPBEGINIF(TO_CHAR(SYSDATE,'DY')IN('SAT','SUN')ORTO_CHAR(SYSDATE,'HH24')<'08'ORTO_CHAR(SYSDATE,'HH24')>='

16、17'THENRAISE_APPLICATION_ERROR(-20500，'非法时间修改表错误！');ENDIF;END;UPDATEEMPSETSAL=3000WHEREEMPNO=7369;标准实用文档【练习1】设计一个语句级触发器，限定只能对数据库进行修改操作，不能对数据库进行插入和删除操作。在需要进行插入和删除时，将触发器设置为无效状态，完成后重新设置为生效状态。CREATEORREPLACETRIGGERonlyupdateBEFOREUPDATEORINSERTORDELETEONEMPBEGINIFdeletingorinsertingthenRAISE

17、_APPLICATION_ERROR（-20500,'非法操作!）;ENDIF;END;7.3【训练1】CREATEORREPLACETRIGGERNODROP_EMPBEFOREDROPONSCHEMABEGINIFSys.Dictionary_obj_name='EMP'THENRAISE_APPLICATION_ERROR（-20005,'错误信息：不能删除emp表！'）;ENDIF;END;标准实用文档DROPTABLEemp;a3i.等脩1尸I士”i.i1，in”*(3Itt4KvimFLhH'fflWH.口?««.

18、9WHHlMMa*%.'H胃7.4【训练1】CREATEVIEWempnameASSELECTenameFROMemp;标准实用文档CREATEORREPLACETRIGGERchange_nameINSTEADOFINSERTONemp_nameDECLAREV_EMPNONUMBER(4);BEGINSELECTMAX(EMPNO)+1INTOV_EMPNOFROMEMP;INSERTINTOemp(empno,ename)VALUES(V_EMPNO,:new.ename);END;标准实用文档INSERTINTOemp_nameVALUES('BROWN');COMMIT;|小MM»【训练2】CREATEORREPLACETRIGGERdelete_from_enameINSTEADOFDELETEONemp_nameBEGINRAISE_APPLICATION_ERROR(-20006,'错误信息：不能在视图中