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

^`

【练习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, '工资修改超出范围,操作取消！');