Oracle 触发器

1. Oracle 触发器

1.1. 定义

触发器在数据库里以独立的对象存储,它与存储过程和函数不同的是,存储过程与函数需要用户显示调用才执行,而触发器是由触发事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。不能接收参数,不能被调用

触发事件包括:

  1. 执行DML 语句(使用INSERT、UPDATE、DELETE 语句对表或视图执行数据处理操作);
  2. 执行DDL语句(使用CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象);
  3. 引发数据库系统事件(如系统启动或退出、产生异常错误等);
  4. 引发用户事件(如登录或退出数据库操作)。

1.2. 语法

create [or replace] trigger tri_name
   [before | after | instead of] tri_event
   on table_name | view_name | user_name | db_name
      [for each row] [when tri_condition]
begin
   plsql_sentences;
end tri_name;
  • before | after | instead of:表示“触发时机”的关键字。before 表示在执行DML 等操作之前触发;after 表示在DML等操作之后发生;instead of 表示触发器为 替代触发器。
  • on:表示操作的数据表、视图、用户模式和数据库等,对它们执行某种数据操作(比如对表执行INSERT、ALTER、DROP 等操作),将引起触发器的运行。
  • for each row:指定触发器为行级触发器,当DML语句对每一行数据进行操作时都会引起该触发器的运行。如果未指定该条件,则表示创建语句级触发器,这时无论数据操作影响多少行,触发器都只会执行一次。
  • tri_condition:表示触发条件表达式。

当触发器被触发时,要使用被插入、更新或删除的记录中的列值,有时要使用操作前、后列的值.
:NEW 修饰符访问操作完成后列的值
:OLD 修饰符访问操作完成前列的值

特性 INSERT UPDATE DELETE
OLD NULL 实际值 实际值
NEW 实际值 实际值 NULL

1.3. 分类

1.3.1. 语句级触发器:

不使用for each row的触发器。无论所触发的DML 语句影响了多少行数据,触发器都只会执行一次。

1.3.2. 行级触发器

使用for each row的触发器。当DML语句对每一行数据进行操作时都会引起该触发器的运行。

1.3.3. 替换触发器

使用instead of关键字的触发器。

1.3.4. 用户事件触发器

用户事件触发器是因进行 DDL 操作或用户登录、退出等操作而引起运行的一种触发器,引起该类型触发器运行的常见用户事件包括:CREATE、ALTER、DROP、ANALYZE、COMMENT、GRANT、REVOKE、RENAME、TRUNCATE、SUSPEND、LOGON 和LOGOFF 等。

1.4. 1例子

1.4.1. 使用触发器实现多表视图的insertupdatedelete

--创建两张测试表
create table temp_a
(
    a1 varchar2(10) primary key ,
    a2 varchar2(10)
);

create table temp_b
(
    a1 varchar2(10) primary key ,
    b1 varchar2(10),
    b2 varchar2(10)
);

--创建测试视图
create view TEMP_c
as
select a.a1 as a1,
       a.a2 as a2,
       b.b1 as b1,
       b.b2 as b2
from temp_a a
         inner join temp_b b on a.a1 = b.a1;
 

--创建触发器
CREATE OR REPLACE TRIGGER TEMP_C_TRIGGER
    INSTEAD OF INSERT OR UPDATE OR DELETE
    ON TEMP_C
    FOR EACH ROW
BEGIN
    IF INSERTING
    THEN
        INSERT INTO TEMP_A(TEMP_A.A1, TEMP_A.A2) VALUES (:NEW.A1, :NEW.A2);
        INSERT INTO TEMP_B(TEMP_B.A1, TEMP_B.B1, TEMP_B.B2) VALUES (:NEW.A1, :NEW.B1, :NEW.B2);
    ELSIF UPDATING
    THEN
        UPDATE TEMP_A
        SET A2=:NEW.A2
        WHERE A1 = :NEW.A1;

        UPDATE TEMP_B
        SET B1=:NEW.B1,
            B2=:NEW.B2
        WHERE A1 = :NEW.A1;
    ELSIF DELETING
    THEN
        DELETE FROM TEMP_A WHERE A1 = :OLD.A1;
        DELETE FROM TEMP_B WHERE A1 = :OLD.A1;
    END IF;
END;

--插入语句
INSERT INTO TEMP_C(A1, A2, B1, B2)
VALUES ('A1_1', 'A2_1', 'B1_1', 'B2_2');

如果没有触发器,直接修改则会报错:

42000][1776] ORA-01776: 无法通过联接视图修改多个基表

1.4.2. 使用触发器监控登录用户行为

--创建日志表
create table temp_log
(
    log_id     varchar2(20) primary key,
    log_time   date,         -- 时间
    log_obj    varchar2(50), --操作对象
    log_action varchar2(50), --操作事件
    log_user   varchar2(50)  --操作用户
);

--创建一个自增序列
create sequence seq_id start with 1000 nomaxvalue cache 20 increment by 1;

--创建事件触发器

create or replace trigger log_trigger
    after ALTER or CREATE or DROP
    ON exercise.schema
begin
    insert into TEMP_LOG(log_id, log_time, log_obj, log_action, log_user)
    VALUES (SEQ_ID.nextval, systimestamp, ORA_DICT_OBJ_NAME, ORA_SYSEVENT, ORA_LOGIN_USER);

end;

--新增
create table temp_v
(
    a1 varchar2(10) primary key,
    b1 varchar2(10),
    b2 varchar2(10)
);
/*
ORA_DICT_OBJ_NAME 操作所对应的数据库对象。
ORA_SYSEVENT 获取触发器的系统事件名。
ORA_LOGIN_USER 获取登录用户名。

schema:
A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema.
*/

--监控用户DML操作
--SELECT 不能作为触发事件
create or replace trigger log_dml_trigger
    before ALTER or CREATE or DROP
    ON exercise.schema
begin
    insert into TEMP_LOG(log_id, log_time, log_obj, log_action, log_user)
    VALUES (SEQ_ID.nextval, systimestamp, ORA_DICT_OBJ_NAME, ORA_SYSEVENT, ORA_LOGIN_USER);
end;


--登录监控触发器
create or replace trigger log_login_trigger
    after LOGON
    ON exercise.schema
begin
    insert into TEMP_LOG(log_id, log_time, log_obj, log_action, log_user)
    VALUES (SEQ_ID.nextval, systimestamp, ORA_DICT_OBJ_NAME, ORA_SYSEVENT, ORA_LOGIN_USER);
end;

1.5. 参考

oracle触发器
oracle触发器
oracle触发器

3 声望
1 粉丝
0 条评论
推荐阅读
ETL 引擎 engine 适配 elasticsearch
etl-engine 已经提供 读elasticsearch节点、写elasticsearch节点(测试环境elastic V8.5.2、elastic V7.17.6)通过etl-engine 可方便实现对关系型数据库、NoSQL之间相互数据交换能力及数据分发能力。========= 分...

weigeonlyyou阅读 757

封面图
Oracle数据库安装配置详细教程汇总(含11g、12c、18c、19c、21c)
不论你是数据库小白,还是久经沙场的技术专家,你接触和运维Oracle数据库的第一步可能都是安装配置。并且随着软硬件的升级、替换以及业务场景的变化,数据库安装也将是你常常会进行的操作之一。

墨天轮阅读 576

2022年最新数据库经典面试题及答案汇总(含PostgreSQL、Oracle、MySQL)
随着企业数字化需求的增加,数据库行业发展日益壮大,企业对DBA岗位的需求也处于逐步增加中。我们梳理了墨天轮平台上2022年最新的一批数据库经典面试题,主要包含PostgreSQL、MySQL和Oracle、数据仓库等方面的内...

墨天轮阅读 397

报告解读下载 | 11月《中国数据库行业分析报告》发布,精彩抢先看
为了帮助大家及时了解中国数据库行业发展现状、梳理当前数据库市场环境和产品生态等情况,从2022年4月起,墨天轮社区行业分析研究团队出品将持续每月为大家推出最新《中国数据库行业分析报告》,持续传播数据技术...

墨天轮阅读 375

Oracle、MySQL等数据库故障处理优质文章分享 | 10月文章汇总
墨天轮社区于9月起持续举办【聊聊故障处理那些事儿】DBA专题征文活动,每月进行评优发奖,鼓励大家记录工作中遇到的数据库故障处理过程,不仅用于自我复盘与分析,同时也能帮助其他的同仁们避坑。

墨天轮阅读 338

数据库日常实操优质文章分享(含Oracle、MySQL等) | 11月刊
本文为墨天轮社区整理的2022年11月国产数据库大事件和重要产品发布消息。目录11月国产数据库大事记(时间线)产品/版本发布兼容认证排行榜新增数据库厂商活动相关资料11月国产数据库大事记(时间线)11月1日,国...

墨天轮阅读 292

不背锅运维-实践笔记:Oracle表按天分区
手动分区的实践,本文的仅拿Zabbix的TRENDS表作为讲解,要对其他表做分区,是一样的套路。 还有,在做这些操作前,请停止上层应用(Zabbix),如果是线上环境,请在合规的时间进行操作。

不背锅运维阅读 221

封面图
3 声望
1 粉丝
宣传栏