华南农业大学大数据库系统概念实验报告材料七.docx
- 文档编号:4122338
- 上传时间:2022-11-28
- 格式:DOCX
- 页数:31
- 大小:692.89KB
华南农业大学大数据库系统概念实验报告材料七.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;
出现问题
解决方案
(列出遇到的问题及其解决方法)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 华南 农业大学 数据库 系统 概念 实验 报告 材料