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. 使用触发器实现多表视图的insert，update和delete

--创建两张测试表
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触发器