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触发器

阅读 2.4k
3 声望
1 粉丝
0 条评论
你知道吗?

3 声望
1 粉丝
文章目录
宣传栏