触发器与job的配合使用oracle,oracle 对象管理 10_触发器job

一、触发器相关概念

1.触发器分类

a.DML触发器:对DML语句进行触发(表上进行DML操作前BEFORE、后AFTER)

b.INSTEAD OF 触发器:只适用于视图(复杂视图上DML)，不能指定BEFORE和AFTER选项，必须指定FOR EACH ROW

c.系统事件触发器：系统的事件触发，便于系统跟踪,监测(启动，关闭，登陆，断开，错误消息等)

d.触发频率，语句级(STATEMENT)触发器和行级(ROW)触发器

2.触发器代码大小不能超过32k，执行部分只能包含DML，而不能包含DDL以及事务控制语言(COMMIT,ROLLBACK,SAVEPOINT)

3.用途：用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

a.将更新前的数据另存备份

4.触发条件谓词

a.INSERTING：如果触发语句是INSERT 语句，则为TRUE,否则为FALSE

b.UPDATING：如果触发语句是UPDATE语句，则为TRUE,否则为FALSE

c.DELETING：如果触发语句是DELETE 语句，则为TRUE,否则为FALSE

5.触发器与对应的触发操纵一块提交和回滚

二、触发器创建及使用

1.备份老数据，记录操作信息

CREATE TABLE emp_new_back AS SELECT * FROM emp_new WHERE 1=2;

CREATE TABLE mess_emp (user_name  varchar2(30),iP VARCHAR2(30), caozuo  VARCHAR2(20),shijian  DATE);

CREATE OR REPLACE TRIGGER TRI_02

BEFORE UPDATE OR INSERT OR DELETE ON EMP_NEW

FOR EACH ROW

DECLARE

IP_INF    VARCHAR2(30);

USER_NAME VARCHAR2(30);

BEGIN

SELECT USER INTO USER_NAME FROM DUAL;

SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO IP_INF FROM DUAL;

CASE

WHEN INSERTING THEN

INSERT INTO MESS_EMP

VALUES

(USER_NAME, IP_INF, 'inserting', SYSDATE);

INSERT INTO EMP_NEW_BACK VALUES (:NEW.ID, :NEW.NAME, :NEW.SALARY);

WHEN UPDATING THEN

INSERT INTO MESS_EMP VALUES (USER_NAME, IP_INF, 'updating', SYSDATE);

INSERT INTO EMP_NEW_BACK

VALUES

(:OLD.ID || ' <2> ' || :NEW.ID,

:OLD.NAME || ' <2> ' || :NEW.NAME,

:OLD.SALARY || ' <2> ' || :NEW.SALARY);

WHEN DELETING THEN

INSERT INTO MESS_EMP VALUES (USER_NAME, IP_INF, 'deleting', SYSDATE);

INSERT INTO EMP_NEW_BACK VALUES (:OLD.ID, :OLD.NAME, :OLD.SALARY);

END CASE;

END;

2.触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退

3.定义某个字段值改动时触发

CREATE OR REPLACE TRIGGER TR_SAL_CHANGE

AFTER UPDATE OF SAL ON EMP

FOR EACH ROW

--WHEN (old.job='CLERK')

DECLARE

V_TEMP INT;

BEGIN

SELECT COUNT(*) INTO V_TEMP FROM AUDIT_EMP_CHANGE WHERE NAME = :OLD.ENAME;

IF V_TEMP = 0 THEN

INSERT INTO AUDIT_EMP_CHANGE VALUES (:OLD.ENAME, :OLD.SAL, :NEW.SAL, SYSDATE);

ELSE

UPDATE AUDIT_EMP_CHANGE

SET OLDSAL = :OLD.SAL, NEWSAL = :NEW.SAL, TIME = SYSDATE

WHERE NAME = :OLD.ENAME;

END IF;

END;

4.限制对Departments表修改(包括INSERT,DELETE,UPDATE)的时间范围，即不允许在非工作时间修改departments表。

CREATE OR REPLACE TRIGGER trig_004

BEFORE INSERT OR UPDATE OR DELETE

ON emp_new

BEGIN

IF ((to_char(SYSDATE,'DAY') IN ('星期六','星期日')) OR

(to_char(SYSDATE,'HH24:mi') NOT BETWEEN '08:30' AND '17:30') ) THEN

RAISE_APPLICATION_ERROR(-20001,'非工作时间，不允许修改emp_new表');

END IF;

END;

5.系统事件触发器

CREATE TABLE ddl_event

(crt_date timestamp PRIMARY KEY,

event_name VARCHAR2(20),

user_name VARCHAR2(10),

obj_type VARCHAR2(20),

obj_name VARCHAR2(20));

--创建触犯发器

CREATE OR REPLACE TRIGGER trig_006

AFTER DDL ON SCHEMA

BEGIN

INSERT INTO ddl_event VALUES

(systimestamp,ora_sysevent, ora_login_user,

ora_dict_obj_type, ora_dict_obj_name);

END trig_006;

--创建登录、退出触发器。

CREATE TABLE log_event

(user_name VARCHAR2(10),

address VARCHAR2(20),

logon_date timestamp,

logoff_date timestamp);

--创建登录触发器

CREATE OR REPLACE TRIGGER tr_logon

AFTER LOGON ON DATABASE

BEGIN

INSERT INTO log_event (user_name, address, logon_date)

VALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logon;

--创建退出触发器

CREATE OR REPLACE TRIGGER tr_logoff

BEFORE LOGOFF ON DATABASE

BEGIN

INSERT INTO log_event (user_name, address, logoff_date)

VALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logoff;

6.禁用触发器当触发器被禁用后，则表上的DML操作将不会触发该触发器，直到该触发器被解除禁用

alter trigger tr_emp_sal disable;

7.启用触发器 被禁用的触发器可以被解除禁用(alter trigger trigger_name enable)

alter trigger tr_emp_sal enable;

8.重新编译触发器

alter trigger tr_emp_sal compile;

9.删除触发器

drop trigger tr_emp_sal;

10.触发器相关数据字典

SELECT * from USER_TRIGGERS;

SELECT * from ALL_TRIGGERS;

SELECT * from DBA_TRIGGERS;

三、oracle定时任务JOB

用于定时执行oracle任务

1、JOB的使用

定义存储过程p_dosomething

定义JOB

declare

jobno number;

begin

dbms_job.submit(

jobno,

'p_dosomething;',                                  --what

to_date('20090101020000','yyyy-mm-dd hh24:mi:ss'), --next_date,可以不填

'Interval时间字符串'                               --interval,关键设置

);

commit;

end;

3、JOB时间段的设置

每分钟执行

Interval => TRUNC(sysdate,'mi') + 1 / (24*60)

每天定时执行,例如：每天的凌晨2点执行

Interval => TRUNC(sysdate) + 1 +2 / (24)

每周定时执行,例如：每周一凌晨2点执行

Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天

每月定时执行,例如：每月1日凌晨2点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24

每季度定时执行,例如每季度的第一天凌晨2点执行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24

每半年定时执行,例如：每年7月1日和1月1日凌晨2点

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24

每年定时执行,例如：每年1月1日凌晨2点执行

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24

参考资料：

http://www.cnblogs.com/emmy/archive/2010/12/27/1918337.html