Oracle触发器

一、DML触发器

1、Before语句触发器

 CREATE OR REPLACE TRIGGER 触发器名

BEFORE UPDATE

建立BEFORE语句触发器

禁止工作人员在休息日修改雇员信息

CREATE OR REPLACE TRIGGER tr_sec_emp

BEFORE INSERT OR UPDATE OR DELETE ON emp

BEGIN

IF to_char(syatem,'DY','nls_date_language=AMERICAN')

IN('SAT','SUN') THEN

raise_application_error(-20001,'不能再休息日改变雇员信息');

END IF;

END;

2.使用条件谓词：

当在触发器中同时包含多个触发事件(INSERT,UPDATE,DELETE)时，为了在触发器代码中区分具体的触发事件，可以使用三个条件谓语。

INSERTING：当触发事件时INSERT操作时，该条件谓词返回值为TRUE,否则为FAULT.

UPDATING:  当触发事件时UPDATE操作时，该条件谓词返回值为TRUE,否则为FAULT.

DELETING:  当触发事件时DELETE操作时，该条件谓词返回值为TRUE,否则为FAULT.

 

CREATE OR REPLACE TRIGGER tr_sec_emp

BEFORE INSERT OR UPDATE OR DELETE ON emp

BEGIN

IF to_char(syatem,'DY','nls_sate_language=AMERICAN') IN ('SAT','SUN')THEN

CASE

WHEN INSERTING THEN

raise_application_error(-20001,'不要再休息日增加雇员');

WHEN UPDATING THEN

raise_application_error(-20001,'不要再休息日修改雇员');

WHEN DELETING THEN

raise_application_error(-20001,'不要再休息日解雇雇员');

 END CASE;

END IF;

END;

 

3.建立AFTER语句触发器

为了审计DML操作，或者在DML操作之后执行汇总运算，例如为了审计在EMP表上DML的操作次数，可以建立AFTER触发器，在建立AFTER触发器之前，首先建立审计表audit_table：

 

CREATE TABLE audit_table(name VARCHAR2(20),ins INT,upd INT,del INT,starttime DATE,

endtime DATE);

 

为了审计在EMP表上DML操作执行次数，最早执行时间和最近执行时间，需要建立AFTER语句触发器

 CREATE OR REPLACE TRIGGER tr_audit_emp

AFTER INSERT OR UPDATE OR DELETE ON emp

DECLARE

v_temp INT;

BEGIN

SELECT count(*) INTO v_temp FROM audit_table WHERE name='EMP';

IF v_temp=0 THEN

INSERT INTO audit_table VALUES('EMP',0,0,0,SYSDATE,NULL);

END IF;

CASE

WHEN INSERTING THEN

UPDATE audit_table SET ins=ins+1,endtime=SYSDATE WHERE name='EMP';

WHEN UPDATE THEN

UPDATE audit_table SET upd=upd+1,endtime=SYSDATE WHERE name='EMP';

WHEN DELETING THEN

UPDATE audit_table SET DEL=DEL+1,endtime=SYSDATE WHERE name='EMP';

END CASE;

END;

二、行触发器 

CREATE OR REPLACE TRUGGER 触发器名

BEFORE INSERT ON 表名

[REFERENCING OLD AS old | NEW AS new]

FOR EACH ROW

[WHEN condition]

PL/SQL块;

 

1.建立BEFORE行触发器

在开发数据库应用时，为了确保数据符合商业逻辑或企业规则，应该使用约束对输入数据加以限制，但某些情况下使用约束可能无法实现复杂的商业逻辑或企业逻辑，此时可以考虑使用BEFORE行触发器。

 下面以确保雇员工资不能低于其原有工资为例：

CREATE OR REPLACE TRIGGER tr_emp_sal

BEFORE UPDATE OF sal ON emp FOR EACH ROW

BEGIN

IF :new.sal<:old.sal THEN

raise_application_error(-20010,'工资只涨不降');

END IF;

END;

在建立触发器tr_emp_sal之后，如果雇员新工资低于其原有工资，则会提示错误信息。

2.建立AFTER行触发器

为了审计DML操作，可以用语句触发器；

而为了审计数据变化，则应该用AFTER行触发器。

下面以审计雇员工资变化为例，首先建立存放审计数据的表audit_emp_change.

CREATE TABLE audit_emp_change(name VARCHAR2(10),oldsal NUMBER(6,2)，newsal NUMBER(6,2),time DATE)

为了深究所有雇员的工资变化和雇员工资的更新日期，必须要建立AFTER触发器。

CREATE OR REPLACE TRIGGER tr_sal_change

AFTER UPDATE OF sal ON temp FOR EACH ROW

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;

当建立触发器tr_sal_change之后，当修改雇员工资时，会将每个雇员的工资变化全部写入到审计表audit_emp_change中。

3.限制行触发器

当使用行触发器时，默认情况下会在每个被作用上执行一次触发器代码。为了使在特定条件下执行行触发器代码。就需要使用WHEN子句对触发条件加以限制：

下面以审计岗位为'saleman'的雇员工资变化为例：

CREATE OR REPLACE TRIGGER tr_sal_change

AFTER UPDATE OF sal ON emp FOR EACH ROW WHEN (old.job='SALEMAN')

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,SYSTEM);

ELSE

UPDATE audit_emp_change SET oldsal=:old.sal,newsal=:new.sal,time=SYSTEMDATE WHERE name=:old.ename;

END IF;

END;

/

建立触发器时，因为使用WHEN子句制定了触发条件没所以只有在满足触发条件才会执行触发器代码。

 4.DML触发器使用注意：

 

如果要基于EMP表建立触发器，那么该触发器的执行代码不能包含对EMP表的查询操作。尽管在建立触发器时不会出现任何错误，但在执行相应触发操作时会显示错误信息。

 

CREATE OR REPLACE TRIGGER tr_emp_sal

BEFORE UPDATE OF sal ON emp FOR EACH ROW

DECLARE

maxsal NUMBER(6,2);

BEGIN

SELECT max(sal) INTO maxsal FROM emp;

IF :now.sal>maxsal THEN

raise_application_error(-20010,'超出了工资上限');

END IF;

END;

三、使用DML触发器

前提：为了确保数据库数据满足特定的商业规则或企业逻辑，可以使用约束、触发器和子程序实现。
因为约束是最好的，实现最简单，所以首选约束。
约束无法完成的，就要触发器。
DML触发器可以用于实现数据安全保护，数据审计，数据完整性，参照完整性，数据复制等功能

1.控制数据安全

在服务及控制数据安全是通过授予和收回对象权限来完成的，例如为了使得SMITH用户可以再SCOTT.EMP表上执行DML操作和SELECT操作，必须为SMITH用户授予相应的对象权限。
CONN SCOTT/TIGGER
GRENT SELECT,INSERT,UPDATE,DELETE ON emp TO SMITH;

当用户具有了以上对象权限之后，就可以随时在EMP表上执行相应的SQL操作，为了实现更复杂的安全模型(例如限制要修改的数据，修改时间等)，就需要使用DML触发器。

下面以限制用户在正常工作时间改变EMP表数据为例：

CREATE OR REPLACE TRIGGER tr_emp_time
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
  IF to_char(SYSDATE,'HH24') NOT BETWEEN '9' AND '17' THEN
    raise_application_error(-20101,'非工作时间');
  END IF;
END;
/

建立了触发器tr_emp_time之后，只能在9点到17点之间对emp表进行DML操作。否则会有错误信息。

 

2.实现数据审计
审计可以用于监视非法和可疑的数据库活动，Oracle数据库本身提供了审计功能，例如，如果要对EMP表上的DML操作进行审计，可以执行：
AUDIT INSERT,UPDATE,DELETE ON emp
BY ACCESS;

如果在emp表删执行DML语句时，Oracle会将关于SQL操作写入到数据字典中。
但！！！！！这种审计只能审计SQL操作，而不会记载数据变化。

为了审计SQL操作引起的数据变化。必须要使用DML触发器。

CREATE OR REPLACE TRIGGER tr_sal_change
AFTER UPDATE OF sal ON emp FOR EACH ROW
DECLARE
v_temp INT;
BEGIN
  SELECT count(*) INTO v_temp FROM audit_emp_change WHERE name:=lod.name;
  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;
/在建立了触发器之后，当修改雇员工资，会将每个雇员的工资变化全部写入到审计表auditz-emp_change中。

 3.实现数据完整性
数据完整性用于确保数据库满足特定的商业或企业规则，数据库完整性可以使用约束，触发器和子程序实现。因为约束的实现最简单，性能也很好，所以首选约束：
例如：为了限制雇员工资不能低于800元，可以选用CHECK约束;
ALTER TABLE emp ADD CONSTRAINT ck_sal
CHECK(sal>=800);

但事实上某些情况约束无法实现特定的商业规则，此时可以使用触发器来实现数据完整性。
例如，假定雇员新工资不能低于其原工资，但不能高出原工资的20%，就要用触发器实现：

CREATE OR REPLACE TRIGGER tr_check_sal
BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal<old.sal OR new.sal>1.2*old.sal)
BEGIN
  raise_application_error('工资只升不降，且不高于20%哦！');
END;
/建立了触发器tr_check_sal之后，如果雇员新工资不符合相应规则，则会提示错误信息。

 4.实现参照完整性
参照完整性是指若两个表之间具有主从关系（主外键关系），当删除主表数据时，必须确保相关的从表数据已经被删除；当修改主表主键列数据时，必须确保相关从表数据已被修改。为了实现联级删除，可以在定义外部键约束时指定ON DELETE CASCADE关键字。

ALTER TABLE emp ADD CONSTRAINT fk_deptno

FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE;

如上所示，建立了约束之后，在删除主表DEPT的数据时，会同时删除从表EMP的所有相关数据。
但用约束不能实现级联更新，如果要更新DEPT表的部门号，则会显示错误信息。

 

错误原因时emp表包含有该部门的相应雇员。为了实现级联更新，可以使用触发器。
CREATE OR REPLACE TRIGGER tr_update_cascade
AFTER UPDATE OF deptno ON dept
FOR EACH ROW
BEGIN
  UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
END;

前提：对于简单的视图，可以直接进行DML操作，但是对于复杂视图，不允许直接执行DML操作，当视图符合以下任何一种情况都不可以：

具有集合操作符(UNION,UNION ALL,INTERSECT,MINUS)；

具有分组函数(MIN,MAX,SUM,AVG,COUNT)；

具有GROUP BY,CONNECT BY 或START WITH子句；

具有DISTINCT关键字

具有连接查询

在具有以上情况的复杂视图执行DML操作，必须要基于视图建立INSTEAD OF触发器。

建立之后，就可以基于复杂视图执行DML语句

 

注意事项：

INSTEAD OF选项只适用于视图

当基于视图建立触发器时，不能指定BEFORE和AFTER选项

在建立视图时没有指定WITH CHECK OPTION选项

当建立INSTEAD OF触发器时，必须指定FOR ECH ROW选项

 

四、建立INSTEAD OF触发器

1.建立复杂视图dept_emp

视图时逻辑表，本身没有任何数据。

视图只是对应一条SELECT语句。

当查询视图时，其数据实际是从视图基表上取得。

为了简化部门及其雇员信息的查询，应建立复杂视图dept_emp

 CREATE OR REPLACE VIEW dept_emp AS

SELECT a.deptno,a.dname,b.empno,b.ename FROM dept a,emp b WHERE a.deptno=b.deptno;

 

当执行以上语句之后，直接查询视图会显示相关信息，但不允许DML操作。

为了可以在复杂视图上执行DML操作，必须要基于复杂视图来建立INSTEAD OF触发器。

下面以复杂视图dept_emp上执行INSERT 操作为例：

CREATE OR REPLACE TRIGGER tr_instead_of_dept_emp

INSTEAD OF INSERT ON dept_emp FOR EACH ROW

DELARE

v_temp INT;

BEGIN

SELECT count(*) INTO v_temp FROM dept WHERE deptno=：new.deptno;

IF v_temp=0 THEN

INSERT INTO dept(deptno,dname) VALUES(:new.deptno,:new.dname);

END IF;

SELECT count(*) INTO v_temp FROM emp WHERE empno=:new.empno;

IF v_temp=0 THEN

INSERT INTO emp(empno,ename,deptno) VALUES(:new.empno,:new.ename,:new.deptno);

EEND IF;

END;

/当建立了此触发器后，就可以在复杂视图上进行INSERT操作。

INSERT INTO dept_emp VALUES(50,'ADMIN','1223','MARY');

INSERT INTO dept_emp VALUES(10,'ADMIN','1224','BARK');

...

执行了以上两条数据之后，为DEPT表插入一条数据，为EMP表插入两条数据。。。

五、建立系统触发器要用到的：

前提要：系统时间触发器是指基于Oracle系统事件(LOGIN登录  STARTUP启动)所建立的触发器，通过使用系统事件触发器，提供了跟踪系统或数据库变化的机制。

1.常用事件属性函数

建立系统触发器要用到的：

ora_client_ip_address:用于返回客户端的IP地址

ora_database_name：用于返回当前数据库名

ora_des_encrypted_password:用于返回DES加密后的用户口令

ora_dict_obj_name:用于返回DDL操作所对应的数据库对象名

ora_dict_obj_name_list(name_list_ OUT ora_name_list_t):用于返回字事件中被修改的对象名列表

ora_dict_obj_owner:用于返回DDL操作所对应的对象的所有者名。

ora_dict_obj_ower_list(ower_list OUT ora_name_list_t):用于返回在事件中被修改对象的所有者列表

ora_dict_obj_type:用于返回DDL操作所对应的数据库对象的类型。

ora_grantee(user_list OUT ora_name_list_t):用于返回授权时事件授权者。

ora_instance_num:用于返回历程号。

ora_is_alter_column(column_name IN VARCHAR2):用于检测特定列是否被修改

ora_is_creating_nested_table:用于检测是否正在建立嵌套表

ora_is_drop_column(column_name IN VARCHAR2):用于检测特定列是否被删除

ora_is_servererror(error_number):用于检测是否返回了特定Oracle错误。

ora_login_user：用于返回登录用户名

ora_sysevent ：用于返回触发 触发器的系统时间名。

 

2.建立例程启动和关闭触发器：

为了跟踪例程启动和关闭事件，可以分别建立例程启动触发器和历程关闭触发器

为了记载历程启动和或关闭事件和时间，首先建立事件表event_table：

 

conn sys/oracle as sysdba

create table event_table(event varchar2(30),time date);

 

在建立了事件表event_table之后，就可以在触发器中引用该表了。

例程启动触发器和关闭触发器只有特权用户才能建立，例程启动触发器只能使用AFTER关键字，而例程关闭触发器只能使用BEFORE关键字

CREATE OR REPPLACE TRIGGER tr_startup

AFTER STARTUP ON DATABASE

BEGIN

INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);

END;

/

 

CREATE OR REPLACE TRIGGER tr_shutdown

BEFORE SHUTDOWN ON DATABASE

BEGIN

INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);

END;

/

 

在建立了tr_startup触发器之后，当打开数据库之后会执行该触发器相应代码，在建立触发器tr_shutdown之后，在关闭例程之前，会执行触发器的相应代码,但SHUTDOWN ABORT(关闭数据库)不会触发该触发器。

 

 

 3.建立登录和退出触发器

为了记载用户登录和退出事件，可以分别建立登录和退出触发器。为了记载登录用户和退出用户的名称。时间和IP地址，应该首先建立专门存档登录和退出的信息表LOG_TABLE

conn sys/oracle as sysdba

CREATE TABLE log_table(

username VARCHAR2(20),login_time DATE,

logoff_time DATE,address VARCHAR2(20)

);

 

在建立了LOG_TABLE表之后，就可以在触发器中引用该表了。

要用特权身份用户来建立登录和退出触发器，并且登录触发器只能使用AFTER关键字，而退出触发器用BEFORE

 

CREATE OR REPLACE TRIGGER tr_logon

AFTER LOGIN ON DATABASE

BEGIN

INSERT INTO log_table(username,logon_time,address)VALUES(ora_login_user,SYSDATE,

ora_client_ip_address);

END;

/

 

CREATE OR REPLACE TRIGGER tr_logoff

BEFORE LOGOFF ON DATABASE

BEGIN

INSERT INTO log_table(username,logoff_time,address)

VALUES(ora_login_user,SYSTEM,ora_client_ip_address);

END;

/

 

在建立了触发器tr_logon之后，当用户登录到数据库之后，会执行其触发器代码；在建立了触发器tr__logoff之后，当用户断开数据库连接之前，会执行其触发器代码。

 

 

4.建立DDL触发器

 

为了记载系统所发生的DDL事件(CREATE,ALTER,DROP),可以建立DDL触发器，为了记载DDL时间信息，应该建立专门的表，以便存放DDL事件信息。

conn sys/oracle as sysdba

CREATE TABLE event_ddl(

event VARCHAR2(20),username VARCHAR2(10),

owner VARCHAR2(10),obbjname VARCHAR2(20),

objtype VARCHAR2(10),time DATE);

 

在建立了表event_ddl之后，就可以在触发器中引用该表，为了记载DDL事件，应该建立DDL触发器，注意，当建立DDL触发器时，必须使用AFTER关键字。

 

CREATE OR REPLACE TRIGGER tr_ddl

AFTER DDL ON scott.schema

BEGIN

INSERT INTO event_ddl VALUES(ora_sysevent,ora_login_user,ora_dict_obj_owner,

ora_dict_obj_name,ora_obj_type,SYSTEM

);

END;

当建立了触发器tr_dll之后，如果在SCOTT方案对象上执行了DDL操作，则会将该新息记载到表event_ddl中。

 六、管理触发器

1.显示触发器信息

通过查询数据字典视图USER_TRIGGER,可以显示当前用户所包含的所有触发器信息。

conn scott/tiger

SELECT trigger_name,status FROM user_triggers

WHERE table_name='EMP';

 

2.禁止触发器

禁止触发器是指使用触发器临时失效。当触发器处于ENABLE状态时，如果在表上执行DML操作，则就会触发相应的触发器。如果基于INSERT操作建立了触发器，当使用SQL*loader装载大批量数据时就会触发触发器

 

为了加快数据装载速度，应该在装载数据之前禁止触发器！

 

ALTERR TRIGGER tr_check_sal DISABLE;

 

3.激活触发器

使触发器重新生效

ALTERR TRIGGER tr_check_sal ENABLE;

 

4.禁止或激活表额所有触发器

如果在表上同时存在多个触发器，那么使用ALTER TABLE命令可以一次禁止或激活说所有触发器

ALTER TABLE emp DISABLE ALL TRIGGERS;

ALTER TABLE emp ENABLE ALL TRIGGER;

 

5.重新编译触发器

当使用ALTER TABLE 命令修改表结构时，会使其触发器转变为INVALID（无效）状态，在这种情况下，为了使得触发器继续生效，需要重新编译触发器：

ALTER TRIGGER tr_check_sal COMPILE;     --COMPILE是编译的意思

 

6.删除触发器

DROP TRIGGER tr_check_sal;