华南农业大学数据库系统概念实验报告七.docx
《华南农业大学数据库系统概念实验报告七.docx》由会员分享，可在线阅读，更多相关《华南农业大学数据库系统概念实验报告七.docx（31页珍藏版）》请在冰点文库上搜索。
华南农业大学数据库系统概念实验报告七
《数据库系统》实验报告七
学号
姓名
实验时间
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_squINCREMENTBY1
STARTWITH1MAXVALUE9999999NOCYCLENOCACHE;
CREATEORREPLACETRIGGERDML_LOG
BEFORE--触发时间为操作前
DELETEORINSERTORUPDATE--由三种事件触发
ONemp
FOREACHROW--行级触发器
BEGIN
IFINSERTINGTHEN
INSERTINTOlogsVALUES（logs_id_squ.NEXTVAL,'EMP','INSERT',:
new.empno,SYSDATE,USER）;
ELSIFDELETINGTHEN
INSERTINTOlogsVALUES（logs_id_squ.NEXTVAL,'EMP','DELETE',:
old.empno,SYSDATE,USER）;
ELSE
INSERTINTOlogsVALUES（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表的结果。
updateemp
setsal=sal*5
whereename='MARY';
COMMIT;
DELETEFROMempWHEREempno=8001;
COMMIT;
SELECT*FROMLOGS;
【练习2】为DEPT表创建同样的触发器，使用LOGS表进行记录，并检验结果。
CREATEORREPLACETRIGGERDEPT_LOG
BEFORE--触发时间为操作前
DELETEORINSERTORUPDATE--由三种事件触发
ONdept
FOREACHROW--行级触发器
BEGIN
IFINSERTINGTHEN
INSERTINTOlogsVALUES（logs_id_squ.NEXTVAL,'DEPT','INSERT',:
new.deptno,SYSDATE,USER）;
ELSIFDELETINGTHEN
INSERTINTOlogsVALUES（logs_id_squ.NEXTVAL,'DEPT','DELETE',:
old.deptno,SYSDATE,USER）;
ELSE
INSERTINTOlogsVALUES（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_sal
BEFORE
UPDATEOFsal
ONemp
FOREACHROW
WHEN（new.job='CLERK'AND（ABS（new.sal-old.sal）>200））
DECLARE
v_noNUMBER;
BEGIN
SELECTCOUNT（*）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_SAL
BEFORE
UPDATE
ONemp
FOREACHROW
BEGIN
IF:
new.job='CLERK'AND（:
new.sal<500OR:
new.sal>2000）THEN
RAISE_APPLICATION_ERROR（-20001,'工资修改超出范围,操作取消！
'）;
ENDIF;
END;
UPDATEempSETsal=800WHEREempno=7876;
UPDATEempSETsal=450WHEREempno=7876;
COMMIT;
SELECTempno,ename,job,salFROMempWHEREempno=7876;
【练习3】限定对emp表的修改，只能修改部门10的雇员工资。
CREATEORREPLACETRIGGERCHECK_NO
BEFORE
UPDATE
ONemp
FOREACHROW
BEGIN
IF:
new.DEPTNO<>10THEN
RAISE_APPLICATION_ERROR（-20001,'修改的部门不符,操作取消！
'）;
ENDIF;
END;
UPDATEempSETsal=8000WHEREdeptno=20;
UPDATEempSETsal=6789WHEREdeptno=30;
UPDATEempSETsal=888WHEREdeptno=10;
select*fromemp;
【训练4】
CREATETRIGGERCASCADE_UPDATE
AFTER
UPDATEOFdeptno
ONDEPT
FOREACHROW
BEGIN
UPDATEEMPSETEMP.DEPTNO=:
NEW.DEPTNO
WHEREEMP.DEPTNO=:
OLD.DEPTNO;
END;
UPDATEdeptSETdeptno=11WHEREdeptno=10;
COMMIT;
SELECTempno,ename,deptnoFROMemp;
【练习4】建立级联删除触发器CASCADE_DELETE，当删除部门时，级联删除EMP表的雇员记录。
CREATETRIGGERCASCADE_DELETE
AFTER
DELETE
ONDEPT
FOREACHROW
BEGIN
DELETEFROMEMPWHEREEMP.DEPTNO=:
OLD.DEPTNO;
END;
DELETEFROMDEPTWHEREDEPTNO=11;
COMMIT;
SELECT*FROMEMP;
SELECT*FROMDEPT;
【训练5】
CREATEORREPLACETRIGGERINITCAP
BEFOREINSERT
ONEMP
FOREACHROW
BEGIN
:
new.ename:
=INITCAP（:
new.ename）;
END;
INSERTINTOemp（empno,ename,job,sal）VALUES（1000,'BILL','CLERK',1500）;
SELECTename,job,salFROMempWHEREempno=1000;
【练习5】限定一次对雇员的工资修改不超过原工资的10%。
CREATEORREPLACETRIGGERCHECK_SALARY
BEFORE
UPDATEOFSAL
ONEMP
FOREACHROW
BEGIN
IF:
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_TIME
BEFORE
UPDATEORINSERTORDELETE
ONEMP
BEGIN
IF（TO_CHAR（SYSDATE,'DY'）IN（'SAT','SUN'））
ORTO_CHAR（SYSDATE,'HH24'）<'08'
ORTO_CHAR（SYSDATE,'HH24'）>='17'THEN
RAISE_APPLICATION_ERROR（-20500,'非法时间修改表错误！
'）;
ENDIF;
END;
UPDATEEMPSETSAL=3000WHEREEMPNO=7369;
【练习1】设计一个语句级触发器，限定只能对数据库进行修改操作，不能对数据库进行插入和删除操作。
在需要进行插入和删除时，将触发器设置为无效状态，完成后重新设置为生效状态。
CREATEORREPLACETRIGGERonlyupdate
BEFORE
UPDATEORINSERTORDELETE
ONEMP
BEGIN
IFdeletingorinsertingthen
RAISE_APPLICATION_ERROR（-20500,'非法操作！
'）;
ENDIF;
END;
7.3
【训练1】
CREATEORREPLACETRIGGERNODROP_EMP
BEFORE
DROPONSCHEMA
BEGIN
IFSys.Dictionary_obj_name='EMP'THEN
RAISE_APPLICATION_ERROR（-20005,'错误信息：
不能删除emp表！
'）;
ENDIF;
END;
DROPTABLEemp;
7.4
【训练1】
CREATEVIEWemp_nameASSELECTenameFROMemp;
CREATEORREPLACETRIGGERchange_name
INSTEADOFINSERTONemp_name
DECLARE
V_EMPNONUMBER（4）;
BEGIN
SELECTMAX（EMPNO）+1INTOV_EMPNOFROMEMP;
INSERTINTOemp（empno,ename）
VALUES（V_EMPNO,:
new.ename）;
END;
INSERTINTOemp_nameVALUES（'BROWN'）;
COMMIT;
【训练2】
CREATEORREPLACETRIGGERdelete_from_ename
INSTEADOFDELETEONemp_name
BEGIN
RAISE_APPLICATION_ERROR（-20006,'错误信息：
不能在视图中删除emp表的雇员！
'）;
END;
实验七 创建触发器，进行表的同步复制
CREATEORREPLACETRIGGERcopytime
beforeINSertOFDELETEorUPDATEONcc;
BEGIN
ifinsertingthen
insertintocopynewvalues（:
newid）;
elsifdeleting
thendeletecopynewwhereid=:
old.id;
elseupdatecopynewsetid:
=:
newidwhereid=:
oldid;
endif;
END;
出现问题
解决方案
（列出遇到的问题及其解决方法）
升级会员 