实验12　 触发器.doc

实验12 触发器姓名：学号：专业：班级：同组人：实验日期：【实验目的与要求】n 理解什么是触发器n 掌握触发器的设计与使用【实验内容与步骤】相关说明：本实验中前面实验创建的触发器可能对后面的实验产生干扰，若出现这种情况，请把前面触发器删除，再完成后面实验。建议每完成一个实验题后，即将该题相关的触发器删除，以免对后面实验产生干扰。12.0 触发器创建与删除语法1触发器的创建触发器是一种在发生数据库事件时自动运行的PL/SQL语法如下：CREATE OR REPLACE TIGGER触发器名 触发时间 触发事件ON表名FOR EACH ROWBEGINpl/sql语句END2触发器的删除DROP TRIGGER trigger_name12.1 触发器基础以下程序展示的是触发器的基本使用方法，请阅读并理解以下程序代码，给出运行结果。1.创建测试表-drop table employees;create table employees( id number(5), name varchar2(30), salary number(8,2), job_id varchar2(20);2.创建触发器CREATE OR REPLACE TRIGGER secure_emp_1 -这里不能有IS BEFORE INSERT ON employees - 这里没有分号BEGIN IF (TO_CHAR (SYSDATE,DY) IN (星期六,星期天) OR (TO_CHAR(SYSDATE,HH24:MI) NOT BETWEEN 08:00 AND 18:00 ) THEN RAISE_APPLICATION_ERROR(-20500,你只能在工作时间对表进行操作); END IF; END;/3.测试触发器（1）-测试语句insert into employees values (1,a,2222,aaaaa);给出测试结果截图：（2）将系统时间修改为周六，再Insert一条记录，给出并比较两次运行的结果。给出测试结果截图：12.2 使用触发器监控数据更新操作阅读以下程序，理解程序功能，给出运行测试结果。1.创建触发器CREATE OR REPLACE TRIGGER secure_emp_2 BEFORE INSERT OR UPDATE OR DELETE ON employeesBEGIN -如果当前时间是周六或周日 或者时间不在8:00-18:00之间 IF (TO_CHAR (SYSDATE,DY) IN (星期五,星期天) OR (TO_CHAR(SYSDATE,HH24:MI) NOT BETWEEN 08:00 AND 18:00 ) THEN IF DELETING THEN RAISE_APPLICATION_ERROR (-20502,你只能在工作时间删除员工表的数据); ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (-20500,你只能在工作时间插入员工表的数据.); ELSIF UPDATING (SALARY) THEN RAISE_APPLICATION_ERROR (-20503,你只能在工作时间更新员工表的数据); ELSE RAISE_APPLICATION_ERROR (-20504,你只能在工作事件操作员工表的数据.); END IF; END IF;END;/2.运行测试分别运行以下测试语句，给出运行结果。并理解为什么会有这样的结果。insert into employees values (1,a,2222,AD_PRES);insert into employees values (2,b,2222,AD_VP);给出测试结果截图：delete from employees;给出测试结果截图：update employees set salary=3000;给出测试结果截图：12.3 在insert或update中使用:new阅读并理解以下程序，理解其功能，给出运行测试结果。1.创建触发器-在insert或update中使用:new-CREATE OR REPLACE TRIGGER restrict_salary BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW BEGIN - 插入和修改可以使用:new来访问新的数据,修改也可以使用:old来访问旧的数据 IF NOT (:NEW.job_id IN (AD_PRES, AD_VP) AND :NEW.salary 15000 THEN RAISE_APPLICATION_ERROR (-20202,员工不能赚到这么多薪水); END IF;END;2.运行测试-测试语句insert into employees values (1,a,20000,AD_PRES);insert into employees values (2,b,20000,AD_VPs);update employees set salary=30000 ,job_id=xxxx where name=a;给出运行结果：思考：观察运行结果，比较insert和update语句执行的差别。12.4 在delete中使用:old阅读并理解以下程序，理解其功能，给出运行测试结果。1.创建触发器CREATE OR REPLACE TRIGGER restrict_salary_del BEFORE DELETE ON employees FOR EACH ROW BEGIN - 在delete语句中只能使用:old来访问旧的数据 IF (:OLD.job_id IN (AD_PRES, AD_VP) AND (:OLD.salary 15000) THEN RAISE_APPLICATION_ERROR (-20202,该员工不可以删除); END IF;END;2.运行测试-测试语句insert into employees values (2,张三,20000,AD_PRES);delete from employees where name=张三;给出运行结果：12.5 INSTEAD OF类型的触发器阅读并理解以下程序，理解其功能，给出运行测试结果。1.准备工作：创建触发器及相应测试用表-INSTEAD OF类型的触发器drop table departments;create table departments( dept_id number(5) primary key, dept_name varchar2(20);drop table employees;create table employees( employee_id number(5), employee_name varchar2(20), department_id number(5), constraint empl_dept_fk foreign key (department_id) references departments(dept_id);-创建视图create or replace view v_empasselect e.employee_id,e.employee_name,e.department_id,d.dept_namefrom employees e,departments dwhere e.department_id = d.dept_id-创建触发器create or replace trigger new_view_emp_dept instead of insert on v_emp for each rowbegin if inserting then insert into departments values(:new.department_id,:new.dept_name); insert into employees values(:new.employee_id,:new.employee_name,:new.department_id); end if;end;2.运行测试（1）查询视图：select * from v_emp;给出运行结果：（2）插入测试数据-向视图中插入数据insert into v_emp values (171,张六,1000,销售部);（3）结果测试select * from employees;给出运行结果：select * from departments;给出运行结果：12.6 系统级触发器阅读并理解以下程序，理解其功能，给出运行测试结果。1.创建触发器-系统触发器-创建登陆或者退出数据库日志表drop table logs;create table logs(userid varchar2(20),times date,op varchar2(10) 操作类型);-登陆数据库触发器create or replace trigger log_onafter logon on schemabegin insert into logs values(user,sysdate,logon);end;/-退出数据库触发器create or replace trigger log_offbefore logoff on schemabegin insert into logs values(user,sysdate,logoff);end;/2.运行测试(1)-以不同的用户身份登录和退出数据库数次，以获得相关数据如：conn sys/testdb as sysdba; Conn soctt/tiger (2)- A设置日期的显示格式alter session set nls_date_format=YYYY-MM-DD HH_MI_SS;(3)- 查看记录select * from logs;给出运行结果：12.7 触发器相关练习1.实验练习：追踪薪水变动情况请创建一个名称为 change_record的触发器，实现当对emp表更新员工工资之后，将更新纪录保存到表salary_change_record中。具体要求如下：建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来，将数据记录到表salary_change_record（empid,old_salary,new_salary,change_date）中，其中old_salary:用来纪录员工原来的工资，new_salary：用来纪录更新后的工资，change_date：记录更新的系统时间。请给出相应的代码：在表中添加或修改几行数据，而后查看表salary_change_record中数据。请给出测试结果：2编写一个数据库触发器，当任何时候某个部门从dept中删除时，该触发器将从emp表中删除该部门的所有雇员记录。给出相应的代码：CREATE OR REPLACE TRIGGER del_emp_deptnoBEFORE DELETE ON deptFOR EACH ROWBEGINDELETE FROM emp WHERE deptno = :OLD.deptno;END;给出测试结果：3. 映射emp表中每个部门的总人数和总工资（1）-创建映射表CREATE TABLE dept_sal AS SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_salFROM empGROUP BY deptno;DESC dept_sal;（2）-创建触发器，映射emp表中每个部门的总人数和总工资创建一触发器，当emp表中数据改变时，自动修改dept_sal表中数据，使其反映出Emp表中数据的最新状态。请给出程序源码：CREATE OR REPLACE TRIGGER emp_info AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE CURSOR cur_emp IS SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno; BEGIN DELETE dept_sal; -触发时首先删除映射表信息 FOR v_emp IN cur_emp LOOP -DBMS_OUTPUT.PUT_LINE(v_emp.deptno | v_emp.total_emp | v_emp.total_sal); -插入数据 INSERT INTO dept_sal VALUES(v_emp.deptno,v_emp.total_