《数据库系统》实验报告七CREATE SEQUENCE logs_id_squ INCREMENT BY 1START WITH 1 MAXV ALUE 9999999 NOCYCLE NOCACHE;CREATE OR REPLACE TRIGGER DML_LOGBEFORE --触发时间为操作前DELETE OR INSERT OR UPDATE -- 由三种事件触发ON empFOR EACH ROW -- 行级触发器BEGINIF INSERTING THENINSERT INTO logsV ALUES(logs_id_squ.NEXTV AL,'EMP','INSERT',:new.empno,SYSDATE, USER);ELSIF DELETING THENINSERT INTO logsV ALUES(logs_id_squ.NEXTV AL,'EMP','DELETE',:old.empno,SYSDATE, USER);ELSEINSERT INTO logsV ALUES(logs_id_squ.NEXTV AL,'EMP','UPDATE',:new.empno,SYSDATE, USER);END IF;END;INSERT INTO emp(empno,ename,job,sal) V ALUES(8001,'MARY','CLERK',1000);COMMIT;SELECT * FROM LOGS;【练习1】修改、删除刚刚插入的雇员记录，提交后检查LOGS表的结果。

update empset sal = sal*5where ename = 'MARY';COMMIT;DELETE FROM emp WHERE empno = 8001; COMMIT;SELECT * FROM LOGS;【练习2】为DEPT表创建同样的触发器，使用LOGS表进行记录，并检验结果。

CREATE OR REPLACE TRIGGER DEPT_LOGBEFORE --触发时间为操作前DELETE OR INSERT OR UPDATE -- 由三种事件触发ON deptFOR EACH ROW -- 行级触发器BEGINIF INSERTING THENINSERT INTO logsV ALUES(logs_id_squ.NEXTV AL,'DEPT','INSERT',:new.deptno,SYSDATE, USER);ELSIF DELETING THENINSERT INTO logsV ALUES(logs_id_squ.NEXTV AL,'DEPT','DELETE',:old.deptno,SYSDATE, USER);ELSEINSERT INTO logsV ALUES(logs_id_squ.NEXTV AL,'DEPT','UPDATE',:new.deptno,SYSDAT E,USER);END IF;END;INSERT INTO dept(deptno,dname,loc) V ALUES(50,'JIAN','GUANGZHOU');COMMIT;SELECT * FROM LOGS;【训练2】CREATE TABLE logerr(NUM NUMBER(10) NOT NULL,MESSAGE V ARCHAR2(50) NOT NULL );CREATE OR REPLACE TRIGGER log_sal BEFOREUPDATE OF salON empFOR EACH ROWWHEN (new.job='CLERK' AND (ABS(new.sal-old.sal)>200))DECLAREv_no NUMBER;BEGINSELECT COUNT(*) INTO v_no FROM logerr;INSERT INTO logerr V ALUES(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_SALBEFOREUPDATEON empFOR EACH ROWBEGINIF :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_NOBEFOREUPDATEON empFOR EACH ROWBEGINIF :new.DEPTNO<>10 THENRAISE_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_UPDATEAFTERUPDATE OF deptnoON DEPTFOR EACH ROWBEGINUPDATE 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_DELETEAFTERDELETEON DEPTFOR EACH ROWBEGINDELETE 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 INSERTON EMPFOR EACH ROWBEGIN: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 BEFOREUPDATE OF SALON EMPFOR EACH ROWBEGINIF :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_TIMEBEFOREUPDATE OR INSERT OR DELETEON EMPBEGINIF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))OR TO_CHAR(SYSDATE,'HH24')< '08'OR TO_CHAR(SYSDATE,'HH24')>='17' THENRAISE_APPLICATION_ERROR(-20500,'非法时间修改表错误！');END IF;END;UPDATE EMP SET SAL=3000 WHERE EMPNO=7369;【练习1】设计一个语句级触发器，限定只能对数据库进行修改操作，不能对数据库进行插入和删除操作。

在需要进行插入和删除时，将触发器设置为无效状态，完成后重新设置为生效状态。

CREATE OR REPLACE TRIGGER onlyupdateBEFOREUPDATE OR INSERT OR DELETEON EMPBEGINIF deleting or inserting thenRAISE_APPLICATION_ERROR(-20500,'非法操作！');END IF;END;7.3【训练1】CREATE OR REPLACE TRIGGER NODROP_EMPBEFOREDROP ON SCHEMABEGINIF Sys.Dictionary_obj_name='EMP' THENRAISE_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_nameINSTEAD OF INSERT ON emp_nameDECLAREV_EMPNO NUMBER(4);BEGINSELECT MAX(EMPNO)+1 INTO V_EMPNO FROM EMP;INSERT INTO emp(empno,ename)V ALUES(V_EMPNO,:new.ename);END;INSERT INTO emp_name V ALUES('BROWN');COMMIT;【训练2】CREATE OR REPLACE TRIGGER delete_from_enameINSTEAD OF DELETE ON emp_nameBEGINRAISE_APPLICATION_ERROR(-20006,'错误信息：不能在视图中删除emp表的雇员！');END;实验七创建触发器，进行表的同步复制CREATE OR REPLACE TRIGGER copytime before INSert OF DELETE or UPDA TE ON cc;BEGINif inserting theninsert into copynew values(:new id);。