"触发器触发器是一种特殊类型的过程。与普通过程不同的是，过程需要用户显式地调用才执行，而触发器则是当某些事件发生时，由 Oracle 自动执行，也就是隐式执行的。 1、触发器的组成触发器主要由如下几个部分组成：触发事件：引起触发器被触发的事件。如 DML 语句（如 INSERT、UPDA ...."

qiankunpingtai
荆棘从死亡中来，死亡又包含着新生。
乾坤万联程序数据库 oracle • 0 回帖 • 1.1K 浏览 • 5 年前

oracle 进阶（五）触发器的使用

触发器

触发器是一种特殊类型的过程。
与普通过程不同的是，过程需要用户显式地调用才执行，
而触发器则是当某些事件发生时，由 Oracle 自动执行，也就是隐式执行的。

1、触发器的组成

触发器主要由如下几个部分组成：
触发事件：引起触发器被触发的事件。
如 DML 语句（如 INSERT、UPDATE、DELETE）、DDL 语句（如 CREATE、ALTER、DROP）、
数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）等。
触发时机：指定触发器的触发时间。
BEFORE 表示在执行 DML 操作之前触发；
AFTER 表示在执行 DML 操作之后触发。
触发类型：指定触发事件发生后需要执行几次触发器。
即，是语句级触发器（默认）还是行级触发器。
触发对象：包括表、视图、模式、数据库。
触发条件：由 WHEN 子句指定的是一个逻辑表达式。
只有当该表达式的值为 TRUE 时，遇到触发事件才会自动执行触发器，否则即便遇到触发事件也不会执行触发器。

2、触发器类型

屏幕截图.jpg

3、创建触发器的语法

行级和语句级触发器语法：

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name 
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;

INSTEAD OF 触发器语法：

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name --只能定义在视图上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;

系统事件触发器语法

CREATE OR REPLACE TRIGGER [sachema.]trigger_name
{BEFORE|AFTER} 
{ddl_event_list | database_event_list}
ON { DATABASE | [schema.]SCHEMA }
[WHEN condition]
PL/SQL_block | CALL procedure_name;

说明：
其中: ddl_event_list：一个或多个 DDL 事件，事件间用 OR 分开；
database_event_list：一个或多个数据库事件，事件间用 OR 分开；
系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式 (SCHEMA) 之上时，只有模式所指定用户的 DDL 操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。当建立在数据库 (DATABASE) 之上时，该数据库所有用户的 DDL 操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有 ADMINISTER DATABASE TRIGGER 权限。

下面给出系统触发器的种类和事件出现的时机（前或后）：

事件	允许的时机	说明
STARTUP	AFTER	启动数据库实例之后触发
SHUTDOWN	BEFORE	关闭数据库实例之前触发（非正常关闭不触发）
SERVERERROR	AFTER	数据库服务器发生错误之后触发
LOGON	AFTER	成功登录连接到数据库后触发
LOGOFF	BEFORE	开始断开数据库连接之前触发
CREATE	BEFORE，AFTER	在执行 CREATE 语句创建数据库对象之前、之后触发
DROP	BEFORE，AFTER	在执行 DROP 语句删除数据库对象之前、之后触发
ALTER	BEFORE，AFTER	在执行 ALTER 语句更新数据库对象之前、之后触发
DDL	BEFORE，AFTER	在执行大多数 DDL 语句之前、之后触发
GRANT	BEFORE，AFTER	执行 GRANT 语句授予权限之前、之后触发
REVOKE	BEFORE，AFTER	执行 REVOKE 语句收权限之前、之后触犯发
RENAME	BEFORE，AFTER	执行 RENAME 语句更改数据库对象名称之前、之后触犯发
AUDIT / NOAUDIT	BEFORE，AFTER	执行 AUDIT 或 NOAUDIT 进行审计或停止审计之前、之后触发

4、示例

语句级触发器示例：

create or replace trigger tig_dept_time
before insert or update or delete
on DEPT
begin
if( to_char(sysdate,'day') in ('星期六','星期日') or
sysdate not between to_date(to_char(sysdate,'yyyyMMdd')||'08:30:00','yyyyMMdd HH24:MI:SS')
and to_date(to_char(sysdate,'yyyyMMdd')||'18:00:00','yyyyMMdd HH24:MI:SS' ) )
then
raise_application_error(-20001,'不是上班时间，不允许修改dept表');
end if;
end;

行级触发器

--创建行级触发器
create or replace trigger tig_salary_raiu
--触发时机
after
--触发类型，可以有多个，多个用or隔开如insert or update，只有当amount列被更新时，update才会被触发
insert or update of amount
--触发对象
on salary
for each row
begin
     if inserting then
         dbms_output.put_line('插入');
     elsif updating then
            dbms_output.put_line('更新');
     end if;
end;

Instead of 类型触发器

--创建视图
CREATE OR REPLACE VIEW emp_view AS 
SELECT deptno, count(*) total_employeer, sum(sal) total_salary 
FROM emp GROUP BY deptno;

--创建Instead of触发器
CREATE OR REPLACE TRIGGER emp_view_delete
   INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
   DELETE FROM emp WHERE deptno= :old.deptno;
END ;

系统事件触发器

--创建记录表
CREATE TABLE log_event
(user_name VARCHAR2(10),
 address VARCHAR2(20), 
 logon_date date,
 logoff_date date);

--创建登录触发器
CREATE OR REPLACE TRIGGER tig_logon
AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logon_date)
   VALUES (ora_login_user, ora_client_ip_address, sysdate);
END tig_logon;

--创建退出触发器
CREATE OR REPLACE TRIGGER tig_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logoff_date)
   VALUES (ora_login_user, ora_client_ip_address, sysdate);
END tig_logoff;

5、禁用和启用触发器

数据库 TRIGGER 的状态：
有效状态 (ENABLE)：当触发事件发生时，处于有效状态的数据库触发器 TRIGGER 将被触发。
无效状态 (DISABLE)：当触发事件发生时，处于无效状态的数据库触发器 TRIGGER 将不会被触发，此时就跟没有这个数据库触发器 (TRIGGER) 一样。
数据库 TRIGGER 的这两种状态可以互相转换。格式为：

ALTER TIGGER trigger_name [DISABLE | ENABLE ];
ALTER TABLE [schema.]table_name {ENABLE|DISABLE} ALL TRIGGERS;

说明：
ALTER TRIGGER 语句一次只能改变一个触发器的状态，针对触发器本身做出禁用和可用处理，同时针对该触发器在表上是否允许可用做出处理；而 ALTER TABLE 语句则一次能够改变与指定表相关的所有触发器的使用状态，只针对触发器在表上是否允许可用做出处理。

例如：

ALTER TIGGER tig_logon DISABLE;
ALTER TIGGER tig_logon ENABLE;
ALTER TABLE emp DISABLE ALL TRIGGERS; 
ALTER TABLE emp ENABLE ALL TRIGGERS;

6、删除触发器

语法

DROP TRIGGER trigger_name;

说明：
当删除其他用户模式中的触发器名称，需要具有 DROP ANY TRIGGER 系统权限，当删除建立在数据库上的触发器时，用户需要具有 ADMINISTER DATABASE TRIGGER 系统权限。此外，当删除表或视图时，建立在这些对象上的触发器也随之删除。

例如：

DROP TRIGGER tig_logoff;

上一篇 oracle 进阶（四）游标的使用
 目录
 下一篇 oracle 进阶（六）使用补充