实验6触发器与作业综述

1、实验6 触发器与作业姓名：学号：专业：网络工程班级：同组人：无实验日期：2013/11/28【实验目的与要求】理解什么是触发器掌握触发器的设计与使用掌握在Oracle中完成定时作务的方法【实验内容与步骤】相关说明：本实验中前面实验创建的触发器可能对后面的实验产生干扰，若出 现这种情况，请把前面触发器删除，再完成后面实验。建议每完成一个实验题后，即 将该题相关的触发器删除，以免对后面实验产生干扰。6.0触发器创建与删除语法1 触发器的创建触发器是一种在发生数据库事件时自动运行的 PL/SQL语法如下：CREATE OR REPLACE TIGGER触发器名 触发时间 触发事件ON表名FOR EA

2、CH ROWBEGINpl/sql 语句END2 触发器的删除DROP TRIGGER trigger, name6.1 触发器基础以下程序展示的是触发器的基本使用方法，请阅读并理解以下程序代码，给出运行结果。1创建测试表-drop table employees; create table employees( idnu mber(5).n ame varchar2(30),salary nu mber(8,2),job_id varchar2(20)；2创建触发器CREATE OR REPLACE TRIGGER secure_emp_1 -这里不能有 ISBEFORE INSERT ON

3、 employees -这里没有分号BEGINIF (TO_CHAR (SYSDA TE,DY) IN (星期六,星期天)OR (TO_CHAR(SYSDATE,HH24:MI) NOT BETWEEN 08:00 AND 18:00 ) THENRAISE_APPLICATION_ERROR(-20500, 你只能在工作时间对表进行操作);END IF;END;/3测试触发器(1) -测试语句insert into employees values (1,a,2222,aaaaa);给出测试结果截图：EQLinsert into employees ualues (1, a ,2222,da

4、aaa );已血1行。(2) 将系统时间修改为周六，再Insert 一条记录，给出并比较两次运行的结果。给出测试结果截图:SQL insert insert Intointoudlues (1,at2222r)enpioifees values (1, a 1 t2222r aaaad)第1行出现彳ORA-2006: f世口 杲賃罷在工作时间对表逬行揀作ORfi-06512:彳E 嘻COTT.WEdUREEMPJ“，line 30RA-MB88 :融发器*SCOT1,SECURE_EI1P_V执行过程中出错6.2 使用触发器监控数据更新操作阅读以下程序，理解程序功能，给出运行测试结果。1创建触

5、发器CREATE OR REPLACE TRIGGER secure_emp_2BEFORE INSERT OR UPDATE OR DELETE ON employeesBEGIN或者时间不在8:00-18:00之间-如果当前时间是周六或周日IF (TO_CHAR (SYSDA TE,DY) IN (星期五,星期天)OR (TO_CHAR(SYSDATE,HH24:MI) NOT BETWEEN 08:00 AND 18:00) THENIF DELETING THEN你只能在工作时间删除员工表的数据)；ELSIFINSERTING THENRAISE_APPLICATION_ERROR (

6、-20500,你只能在工作时间插入员工表的数据)；ELSIFUPDATING (SALARY) THENRAISE_APPLICATION_ERROR (-20503,你只能在工作时间更新员工表的数据)；ELSERAISE_APPLICATION_ERROR (-20504,你只能在工作事件操作员工表的数据.);RAISE_APPLICATION_ERROR (-20502,END IF;END IF;END;/2.运行测试分别运行以下测试语句，给出运行结果。并理解为什么会有这样的结果。in sert into employees values (1,a,2222,AD_PRES);in se

7、rt into employees values (2,b,2222,AD_VP);给出测试结果截图：S(L delete from Employ电冲；己；m五XdL Idelete from employees;给出测试结果截图：SQL update pinployees set已更新怖*SQL Iupdate employees set salary=3000;给出测试结果截图:6.3 在 insert 或 update 中使用:new阅读并理解以下程序，理解其功能，给出运行测试结果。1创建触发器-在 insert 或 update 中使用：new-CREATE OR REPLACE TR

8、IGGER restrict_salaryBEFORE INSERT OR UPDATE OF salary ON employeesFOR EACH ROWBEGIN-插入和修改可以使用:new来访问新的数据，修改也可以使用：old来访问旧的数据IF NOT (:NEW.job_id IN (AD_PRES, AD_VP)AND :NEW.salary 15000THENRAISE_APPLICATION_ERROR (-20202,员工不能赚到这么多薪水);END IF;END;2.运行测试-测试语句in sert into employees values (1,a,20000,AD_P

9、RES);in sert into employees values (2,b,20000,AD_VPs);update employees set salary=30000 ,job_id=xxxx where n ame=a;给出运行结果S(JL insert into employees values C1,1 a ,20000,1 flD_PRES *): 已创建1行。JQL insert intovaluesb11 UP* ;葛1行出现lRfl-2Q202:IRA-06512：IRAH0UQ88：.nsert intovdlu (2fb ,20090, fl)员工不能赚到这么多薪水在

10、 SCOTT.RESTRICT SALORV, line & 融发器1 SCOTT.RESTRICT_SfiLftRV执行过程中出错MQLA update employees set sailar*/=9 0U0O , job_id= 1 xxxx where riane=1 a update employees set百苗0朋 rjot) id= xjtx where n研m=a#第1行岀现镇误二Rft-20202：贝工不能赚到这么多薪水0RA-Q6&12；在驾CDT-RHIIUCT_WMRY,，line 6Unn-Qll998： 融发器 SCOTT-RESTRlCT SfiLfiR * 执

11、行过程中出错思考：观察运行结果，比较insert和update语句执行的差别。6.4 在 delete 中使用:old阅读并理解以下程序，理解其功能，给出运行测试结果。1创建触发器CREATE OR REPLACE TRIGGER restrict_salary_delBEFORE DELETE ON employees FOR EACH ROWBEGIN-在delete语句中只能使用:old来访问旧的数据IF (:OLD.job_id IN (AD_PRES, AD_VP) AND (:OLD.salary 15000) THENRAISE_APPLICATION_ERROR (-20202

12、,该员工不可以删除)； END IF;END;2.运行测试-测试语句insert into employees values (2,张三,20000,AD_PRES);delete from employees where name=张三;给出运行结果：SQL insert into employees values ,29060,AD_PRE3);已创建1行。SQL delete f kdh empLaypps： uhere name= 弓忙三 delete from enployeesname=HK-魁帕不可嘶第4:0RA-20202:尿0RA-01I088 :触发器 1 SCftTT.

13、RESTFtICT_SftLARV_DEL 执行过程中岀错ORA- 06512 :SCOTT.RESTRICTSALAIIV_DEL,t line 56.5 INSTEAD OF 类型的触发器阅读并理解以下程序，理解其功能，给出运行测试结果。1.准备工作：创建触发器及相应测试用表-INSTEAD OF类型的触发器drop table departme nts;create table departme nts(dept_id nu mber(5) primary key,dept_ name varchar2(20);_drop table employees;create table emp

14、loyees(employee_id nu mber(5),employee, name varchar2(20), departme nt_id nu mber(5),con stra int empl_dept_fk foreig n key(departme nt_id) references departme nts(dept_id)；-创建视图create or replace view v_empasselect e.employee_id,e.employee _n ame,e.departme nt_id,d.dept_ name from employees e,depart

15、me nts dwhere e.departme nt_id = d.dept_id-创建触发器create or replace trigger n ew_view_emp_deptin stead of insert on v_empl for each rowbeginif in sert ing the nin sert into departme ntsvalues(: new.departme nt_id,: new.dept_ name);in sert into employeesvalues(: new.employee_id,: new.employee_ name,: n

16、ew.departme nt_id); end if;end;2.运行测试(1)查询视图：select * from v_emp;给出运行结果：select * from u enp;(2)插入测试数据-向视图中插入数据insert into v_emp values (171,张六,1000,销售部);(3 )结果测试select * from employees;给出运行结果：SQL select * From enploes;EMPLOYEE ID EMPLDVEE NAMEOEPARINENTIDV1抵六1090select * from departme nts;给出运行结果：SQL

17、 select * from departments;DFPT_ID DEPT_HftME10销售部6.6 系统级触发器阅读并理解以下程序，理解其功能，给出运行测试结果。1创建触发器系统触发器 -创建登陆或者退出数据库日志表drop table logs;create table logs( userid varchar2(20), timesdate,opvarchar2(10);-登陆数据库触发器create or replace trigger log_ onafter logo n on schema beginin sert into logs values(user,sysdate

18、,logo n);en d;/-退出数据库触发器create or replace trigger log_offbefore logoff on schema beginin sert into logs values(user,sysdate,logoff);en d;/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) -查看记录

19、select * from logs;给出运行结果：SQL select 辛 FromUSERIDTIMESORSCOTT2013-12-fl02_472logoffSCOTT2913-12-9!；舵匸2。logon6.7 作业与定时任务6.7.1 作业概述作业是Oracle中一个程序包，可用于Oracle中实现定时任务。日常开发最常用到的用于完成定时任务的是 Job中包中的submit函数，其接口如下：1.dbms_job.submit( job out binary_integer,2.whatinarchar2,3.next_dateindate,4.intervalinvarchar2

20、.5.no_parseinboolean)其中: job :输出变量，是此任务在任务队列中的编号； what:执行的任务的名称及其输入参数； next_date :任务执行的时间； interval :任务执行的时间间隔。其中Interval这个值是决定 Job何时，被重新执行的关键；当interval设置为null时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用sysdate+ m 表示。以下实验中创建一个名称为tri_test_id的触发器，用于实现当在作业中定时调用存储过程，向Test表插入数据时，自动的触发序列号的产生，并添加数据到Test表中。更新员

21、工工资之后，将更新纪录保存到表salary_change_record中。6.7.2 实验准备-(1)创建测试表：CREATE TABLE Test(id number,cur_user varchar2(20),cur_time varchar2(30);-(2)创建序列 test_seque nee:CREATE SEQUENCE test_sequenceINCREMENT BY 1-每次加几个STARTWITHNOMAXV ALUE-从1开始计数-不设置最大值NOCYCLE-一直累加，不循环CACHE 10;-建触发器 tri_test_id :CREATE OR REPLACE TR

22、IGGER tri_test_idbefore INSERTON test -test 是表名FOR each rowDECLAREnextid number;BEGINIF :new.id IS NULL or :new.id=0 THEN-id 是列名SELECT test_sequence.nextval- test_sequence 正是刚才创建的序歹INTO nextidFROM sys.dual;:new.id:=nextid;END IF;END tri_test_id;/-创建一个自定义过程CREATE OR REPLACE PROCEDURE proc_testASBEGIN

23、INSERT INTO test(cur_user,cur_time)VALUES(user,to_char(sysdate,YYYY-MM-DD HH24:MI:SS);-系统时间转为格式串END;/6.7.3 作业的创建与使用-创建JOBDECLARE jobNo number;BEGINdbms_job.submit(jobNo,-job 参数proc_test;, -what参数是将被执行的PL/SQL代码块sysdate,-next_date参数指识何时将运行这个工作，本例为马上开始sysdate+1/(24*60*10)-interval 参数，即 1/10 分钟运行 test 过

24、程一次);-no_parse 参数，无END;-next_date,可如：to_date(20110517093500,yyyy-mm-dd hh24:mi:ss),6.7.4 测试-测试：1分钟后查看Test表中数据SELECT *FROM test请给出运行结果：SQL? SELECT *2 RM test3 :耒选定行6.7.5 停止与移除作业若要移除、停止或启用作业，需根据作业号，调用作业包中存储过程 dbmsob.remove(jobNo)来完成。其中，作业号可通过数据字典all_jobs获取。1获取作业号可通过查询数据字典 all_jobs来获取作业号，示例如下：S-3SELECT

25、 joby log_user? last_date94FROM,一其中，job字段值即为作业号。2. 移除作业-删除JOBbegindbms_job.remove(jobNo); -jobNo为作业号，使用时应用查询得到的结果 en d;/3. 停止一个JOBexec dbms_job.broke n(jobNo,true) -jobNo 为作业号，使用时应用查询得到的结果4. 停止一个JOBexec dbms_job.broke n(jobNo,false) -jobNo 为作业号，使用时应用查询得到的结果6.8 实验练习1.实验练习：利用触发器追踪薪水变动情况请创建一个名称

26、为change_record的触发器，实现当对 emp表更新员工工资之后，将更新纪录保存至U表salary_change_record中。具体要求如下：建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来，将数据记录到表salary_cha nge_record(empid,old_salary ,n ew_salary,cha nge_date )中，其中 old_salary:用来纪录员工原来的工 资，new_salary:用来纪录更新后的工资，change_date：记录更新的系统时间。请给出相应的代码：create table

27、salary_cha nge_record(empid nu mber(4),old_salary nu mber(7,2),n ew_salary nu mber(7,2),cha nge_date date);create or replace trigger cha nge_recordafter update on emp for each rowbeginif updat ing the ninsert into salary_cha nge_recordvalues(: new.emp no,:old.sal,: new.sal,sysdate);end if ;en d;/在em

28、p表中添加或修改几行数据，而后查看表salary_change_record中数据。请给出测试结果：5QL select * fromctingp record;EMPID OLD_SfiLARV NEW_SftLftR? CWAHGE_DfiTE79009502013-07-22 C6 G5 16”叫1302 9000 2013-07-22 册0匚佃2.编写一个数据库触发器,当任何时候某个部门从dept表中删除时，该触发器将从emp第21页共15页表中删除该部门的所有雇员记录。给出相应的代码:create or replace trigger del_emp_dept nobefore de

29、lete on dept for each rowbegindelete from emp where dept no=:old.dept no;en d;/给出测试结果：；QL create dp replace trigger del_emp_deptno2 before delete on dppt Fop*row9 bglnMdelete front enp where* deptno=:old.dwplzno;5 end;6 /抽发器己创建；QL delete frcn dept where deptnold;三删除1讦。3. 映射emp表中每个部门的总人数和总工资(1) -创建映射

30、表CREATE TABLE dept_salASSELECT dept no,COUNT(emp no) AS total_emp,SUM(sal) AS total_salFROM empGROUP BY dept no;DESC dept_sal;(2) -创建触发器，映射 emp表中每个部门的总人数和总工资创建一触发器，当 emp表中数据改变时，自动修改dept_sal表中数据，使其反映出Emp表中数据的最新状态。请给出程序源码：CREATE OR REPLACE TRIGGER emp_i nfoAFTER INSERT OR UPDATE OR DELETE ON emp DECLA

31、RE CURSOR cur_emp ISSELECT dept no,COUNT(emp no) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY dept no;BEGIN DELETE dept_sal;FOR v_emp IN cur_emp LOOPDBMS_OUTPUT.PUT_LINE(v_emp.dept no | v_emp.total_emp| v_emp.total_sal);INSERT INTO dept_sal V ALUES(v_emp.deptno,v_emp.total_emp, v_emp.total_s

32、al);END LOOP;END;/ select * from dept_sal;INSERT INTO emp(empno,deptno,sal) V ALUES(123,20,10000);SELECT * FROM dept_sal;DELETE EMP WHERE emp no=123;SELECT * FROM dept_sal;(3)测试按下面步骤完成测试，结出测试结果，并比对测试结果，看是否满足要求。A .查询dept_sal中数据：SELECT * FROM dept_sal;给出结果：S(L select * From dept_sal:DEPTNO TOTAL EMP T

33、OTAL SAL620511576B.-对emp表进行 DML操作INSERT INTO emp(empno,deptno,sal) V ALUES(123,10,10000);SELECT * FROM dept_sal;给出测试结果：SQL INSERT INTO gnp(mpnt) ,de|tnoUAL(JES( 123 ,1 0000);已创建1行.SQL SELECT * FROM dept_sal;OEPTHO TOTAL_EMP T0TflL_5ALHi flruDELETE EMP WHERE emp no =123;SELECT * FROM dept_sal;给出测试结果：

34、SQL DELETE EflP UHERE ?npnv=123;己删除1行.SQL SELECT * FROM dept_5al；DEPTNO TOTfiL_EMP TOTfiL_SfiL30694OB295115764. 监控用户对表的更新操作。已知employees_copy表中一个非常重要的表，当用户其中的数据作修改更新等操作时， 需记录操作用户、时间和所做的操作(即是INSERT/UPDATE/DELETE 中的哪一个触发了触发器)于日志表中employees_log中，请按以下过程完成指定业务。(1)建立试验表create table employees_copyasselect *from hr.employees建立日志表create table employees_log(whowhe nvarchar2(30), date,op_type VARCH