资源描述
^`
《数据库系统》实验报告七
学号
姓名
实验时间
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(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 -- 由三种事件触发
ON emp
FOR EACH ROW -- 行级触发器
BEGIN
IF 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,UPDATE,: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*5
where ename = MARY;
COMMIT;
DELETE FROM emp WHERE empno = 8001;
COMMIT;
SELECT * FROM LOGS;
【练习2】为DEPT表创建同样的触发器，使用LOGS表进行记录，并检验结果。
CREATE OR REPLACE TRIGGER DEPT_LOG
BEFORE --触发时间为操作前
DELETE OR INSERT OR UPDATE -- 由三种事件触发
ON dept
FOR EACH ROW -- 行级触发器
BEGIN
IF 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.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 VARCHAR2(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 emp 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
BEGIN
IF :new.job=CLERK AND (:new.sal<500 OR :new.sal>2000) THEN
RAISE_APPLICATION_ERROR(-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
BEGIN
IF :new.DEPTNO<>10 THEN
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_UPDATE
AFTER
UPDATE OF deptno
ON DEPT
FOR EACH ROW
BEGIN
UPDATE EMP SET EMP.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_DELETE
AFTER
DELETE
ON DEPT
FOR EACH ROW
BEGIN
DELETE FROM EMP WHERE EMP.DEPTNO=:OLD.DEPTNO;
END;
DELETE FROM DEPT WHERE DEPTNO = 11;
COMMIT;
SELECT * FROM EMP;
SELECT * FROM DEPT;
【训练5】
CREATE OR REPLACE TRIGGER INITCAP
BEFORE INSERT
ON EMP
FOR EACH ROW
BEGIN
: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 empno=1000;
【练习5】限定一次对雇员的工资修改不超过原工资的10%。
CREATE OR REPLACE TRIGGER CHECK_SALARY
BEFORE
UPDATE OF SAL
ON EMP
FOR EACH ROW
BEGIN
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 = SAL*1.08 WHERE EMPNO = 7876;
COMMIT;
SELECT * FROM EMP WHERE EMPNO = 7876;
7.2.4【训练1】
CREATE OR REPLACE TRIGGER CHECK_TIME
BEFORE
UPDATE OR INSERT OR DELETE
ON EMP
BEGIN
IF (TO_CHAR(SYSDATE,DY) IN (SAT,SUN))
OR TO_CHAR(SYSDATE,HH24)< 08
OR TO_CHAR(SYSDATE,HH24)>=17 THEN
RAISE_APPLICATION_ERROR(-20500,非法时间修改表错误！);
END IF;
END;
UPDATE EMP SET SAL=3000 WHERE EMPNO=7369;
【练习1】设计一个语句级触发器，限定只能对数据库进行修改操作，不能对数据库进行插入和删除操作。在需要进行插入和删除时，将触发器设置为无效状态，完成后重新设置为生效状态。
CREATE OR REPLACE TRIGGER onlyupdate
BEFORE
UPDATE OR INSERT OR DELETE
ON EMP
BEGIN
IF deleting or inserting then
RAISE_APPLICATION_ERROR(-20500,非法操作！);
END IF;
END;
7.3
【训练1】
CREATE OR REPLACE TRIGGER NODROP_EMP
BEFORE
DROP ON SCHEMA
BEGIN
IF Sys.Dictionary_obj_name=EMP THEN
RAISE_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
DECLARE
V_EMPNO NUMBER(4);
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 copytime
before 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;
出现问题
解决方案
（列出遇到的问题及其解决方法）
展开阅读全文
相关搜索