实验6触发器与作业Word格式文档下载.docx

1、CREATE OR REPLACE TRIGGER secure_emp_1 -这里不能有IS BEFORE INSERT ON employees - 这里没有分号 IF （TO_CHAR （SYSDATE,DY） IN （星期六,星期天） OR （TO_CHAR（SYSDATE,HH24:MI） NOT BETWEEN 08:00 AND 18: ） THEN RAISE_APPLICATION_ERROR（-20500,你只能在工作时间对表进行操作 END IF; END;/3.测试触发器（1）-测试语句insert into employees values （1,a,2222,aaa

2、aa给出测试结果截图：（2）将系统时间修改为周六，再Insert一条记录，给出并比较两次运行的结果。6.3 使用触发器监控数据更新操作阅读以下程序，理解程序功能，给出运行测试结果。1.创建触发器CREATE OR REPLACE TRIGGER secure_emp_2 BEFORE INSERT OR UPDATE OR DELETE ON employees -如果当前时间是周六或周日 或者时间不在8:00-18:00之间星期五 ） THEN IF DELETING THEN RAISE_APPLICATION_ERROR （-20502,你只能在工作时间删除员工表的数据 ELSIF IN

3、SERTING THEN RAISE_APPLICATION_ERROR （-20500,你只能在工作时间插入员工表的数据. ELSIF UPDATING （SALARY） THEN RAISE_APPLICATION_ERROR （-20503,你只能在工作时间更新员工表的数据 ELSE RAISE_APPLICATION_ERROR （-20504,你只能在工作事件操作员工表的数据. END IF;2.运行测试分别运行以下测试语句，给出运行结果。并理解为什么会有这样的结果。AD_PRESinsert into employees values （2,bAD_VPdelete from em

4、ployees;update employees set salary=3000;6.4 在insert或update中使用:new阅读并理解以下程序，理解其功能，给出运行测试结果。-在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 （, ） AND :NEW

5、.salary 15000 THEN RAISE_APPLICATION_ERROR （-20202,员工不能赚到这么多薪水-测试语句,20000,AD_VPsupdate employees set salary=30000 ,job_id=xxxx where name=;给出运行结果：思考：观察运行结果，比较insert和update语句执行的差别。6.5 在delete中使用:oldCREATE OR REPLACE TRIGGER restrict_salary_del BEFORE DELETE ON employees FOR EACH ROW - 在delete语句中只能使用:

6、 IF （:OLD.job_id IN （） AND （:OLD.salary 15000）该员工不可以删除张三delete from employees where name=6.6 INSTEAD OF类型的触发器1.准备工作：创建触发器及相应测试用表-INSTEAD OF类型的触发器drop table departments;create table departments（ dept_id number（5） primary key, dept_name varchar2（20）drop table employees; employee_id number（5）, employee

7、_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-创建触发器crea

8、te or replace trigger new_view_emp_dept instead of insert on v_empl for each rowbegin if inserting then insert into departments values（:new.department_id,:new.dept_name）; insert into employees new.employee_id,:new.employee_name,:new.department_id）; end if;end;（1）查询视图：select * from v_emp;（2）插入测试数据-向视

9、图中插入数据insert into v_emp values （171,张六,1000,销售部（3）结果测试select * from employees;select * from departments;6.7 系统级触发器-系统触发器-创建登陆或者退出数据库日志表drop table logs;create table logs（userid varchar2（20）,times date,op varchar2（10） -登陆数据库触发器create or replace trigger log_onafter logon on schema insert into logs valu

10、es（user,sysdate,logon-退出数据库触发器create or replace trigger log_offbefore logoff on schemalogoff（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;6.8 作业与定时任务6.7.1 作业概述作业是Oracle中一个程序包

11、，可用于Oracle中实现定时任务。日常开发最常用到的用于完成定时任务的是Job中包中的submit函数，其接口如下：1. dbms_job.submit（ job out binary_integer,2. whatinarchar2,3. next_date indate，4. intervalinvarchar2,5. no_parseinboolean）其中：job：输出变量，是此任务在任务队列中的编号；what：执行的任务的名称及其输入参数；next_date：任务执行的时间；interval：任务执行的时间间隔。其中Interval这个值是决定Job何时，被重新执行的关键；当int

12、erval设置为null时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用sysdatem表示。以下实验中创建一个名称为tri_test_id的触发器，用于实现当在作业中定时调用存储过程，向Test表插入数据时，自动的触发序列号的产生，并添加数据到Test表中。更新员工工资之后，将更新纪录保存到表salary_change_record中。6.7.2 实验准备-（1）创建测试表:CREATE TABLE Test（id number,cur_user varchar2（20）,cur_time varchar2（30）;-（2）创建序列 test_sequenc

13、e:CREATE SEQUENCE test_sequence INCREMENT BY 1 -每次加几个 START WITH 1 -从1开始计数 NOMAXVALUE -不设置最大值 NOCYCLE -一直累加，不循环 CACHE 10;-建触发器tri_test_id：CREATE OR REPLACE TRIGGER tri_test_id before INSERT ON test -test 是表名 FOR each row DECLARE nextid number;BEGIN IF :new.id IS NULL or :new.id=0 THEN -id是列名 SELECT

14、test_sequence.nextval - test_sequence正是刚才创建的序列 INTO nextid FROM sys.dual; :new.id:=nextid;END tri_test_id;/ -创建一个自定义过程 CREATE OR REPLACE PROCEDURE proc_test AS BEGIN INSERT INTO test（cur_user,cur_time） VALUES（user,to_char（sysdate,YYYY-MM-DD HH24:MI:SS）; -系统时间转为格式串 END; / 6.7.3 作业的创建与使用-创建JOBDECLARE

15、jobNo number; dbms_job.submit（ jobNo, -job参数 proc_test;, -what参数是将被执行的PL/SQL代码块 sysdate, -next_date,参数指识何时将运行这个工作,本例为马上开始sysdate+1/（24*60*10）-interval参数,即1/10分钟运行test过程一次 ）; -no_parse参数，无 -next_date,可如：to_date（20110517093500yyyy-mm-dd hh24:mi:ss）,6.7.4 测试-测试： 1分钟后查看Test表中数据SELECT *FROM test请给出运行结果：6

16、.7.5 停止与移除作业若要移除、停止或启用作业，需根据作业号，调用作业包中存储过程dbms_job.remove（jobNo）来完成。其中，作业号可通过数据字典all_jobs获取。1.获取作业号可通过查询数据字典all_jobs来获取作业号，示例如下：其中，job字段值即为作业号。2.移除作业-删除JOB dbms_job.remove（jobNo）;-jobNo为作业号，使用时应用查询得到的结果3. 停止一个JOBexec dbms_job.broken（jobNo,true） -jobNo为作业号，使用时应用查询得到的结果4. 停止一个JOBexec dbms_job.broken（j

17、obNo,false） -jobNo为作业号，使用时应用查询得到的结果6.9 实验练习1.实验练习：利用触发器追踪薪水变动情况请创建一个名称为 change_record的触发器，实现当对emp表更新员工工资之后，将更新纪录保存到表salary_change_record中。具体要求如下：建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来，将数据记录到表salary_change_record（empid,old_salary,new_salary,change_date）中，其中old_salary:用来纪录员工原来的工资，new_

18、salary：用来纪录更新后的工资，change_date：记录更新的系统时间。请给出相应的代码：create table salary_change_record（ empid number（4）, old_salary number（7,2）, new_salary number（7,2）, change_date datecreate or replace trigger change_recordafter update on emp for each row if updating then insert into salary_change_record values（:new.e

19、mpno,:old.sal,:new.sal,sysdate end if ;在emp表中添加或修改几行数据，而后查看表salary_change_record中数据。请给出测试结果：2编写一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员记录。给出相应的代码：create or replace trigger del_emp_deptnobefore delete on dept for each row delete from emp where deptno=:old.deptno;给出测试结果：3. 映射emp表中每个部门的总人数和总工

20、资（1）-创建映射表CREATE TABLE dept_sal ASSELECT 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_infoAFTER INSERT OR UPDATE OR DELETE ON emp DE

21、CLARE CURSOR cur_emp ISSELECT deptno,COUNT（empno） AS total_emp,SUM（sal） AS total_sal FROM empBEGIN DELETE dept_sal; FOR v_emp IN cur_emp LOOPDBMS_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_emp,v_emp.total_sal）;END LOOP;select

22、 * from dept_sal;INSERT INTO emp（empno,deptno,sal） VALUES（12320,10000）;SELECT * FROM dept_sal;DELETE EMP WHERE empno=123;（3）测试按下面步骤完成测试，结出测试结果，并比对测试结果，看是否满足要求。A查询dept_sal中数据：给出结果：B-对emp表进行DML操作104. 监控用户对表的更新操作。已知employees_copy表中一个非常重要的表，当用户其中的数据作修改更新等操作时，需记录操作用户、时间和所做的操作（即是INSERT/UPDATE/DELETE中的哪一个触

23、发了触发器）于日志表中employees_log中，请按以下过程完成指定业务。（1） 建立试验表create table employees_copy as select *from hr.employees（2） 建立日志表create table employees_log（ who varchar2（30）, when date,op_type VARCHAR2（15）;（3） 在employees_copy表上建立语句触发器，当对该表进行insert或update或delete操作时，将用户名称、操作时间和操作类型记录到表employees_log 中。请给出源程序：create or replace trigger emptrigger before insert or update or delete on employees_copy for each rowdeclare operation employees_log.op_type%type; operation := insert elsif updating thenupdate elsif deleting thendelete insert into employees_log values（user,sysdate,operation）;