第11章 触发器

target

了解什么是触发器
了解触发器的作用及触发器的类型
掌握触发器的创建
掌握触发器的修改及查看

1. 什么是触发器

学习存储过程后，认识和了解触发器并不复杂，它们之间有着相似之处。

1.1 认识触发器

触发器和存储过程比较类似，它由PL/SQL编写并存储在数据库中，它可以调用存储过程，但触发器本身的调用和存储过程调用却是不一样的。

存储过程由用户、应用程序、触发器或其他过程调用。但触发器只能由数据库的特定事件来触发。所谓的特定事件主要包括如下几种类型的事件。

(1) 用户在指定的表或视图中做DML操作，主要包括如下几种：

  • INSERT操作，在特定的表或视图中増加数据。
  • UPDATE操作，对特定的表或视图修改数据。
  • DELETE操作，删除特定表或视图的数据。

(2) 用户做DDL操作，主要包括如下几种：

  • CREATE操作，创建对象。
  • ALTER操作，修改对象。
  • DROP操作，删除对象。

(3) 数据库事件，主要包括如下几种：

  • LOGON/LOGOFF用户的登录或注销。
  • STARTUP/SHUTDOWN数据库的打开或关闭•
  • ERRORS特定的错误消息等。

在以上事件中的一种或多种发生时就能使触发器运行。

🌰：当员工表中新增一条记录后，自动打印“成功插入新员工”

create or replace trigger insertStaffHint
 after insert on emp_temp 
 for each row
declare

begin

 dbms_output.put_line('新增员工成功');

end insertStaffHint;
/

测试：

SQL> set serveroutput on;
SQL> insert into emp_temp(empno,ename,job,sal,deptno) values(20,'韩梅梅','boss',12345,10);
新增员工成功

已创建 1 行。

1.2 触发器的作用

触发器可以根据不同的事件进行调用，它有着更加精细的控制能力，这种特性可以帮助开 发人员完成很多普通PL/SQL语句完成不了的功能。

  • 自动生成自增长字段。例如，在表中插入数据前得到序列的最大值，避免序列重复。

  • 执行更复杂的业务逻辑。

  • 防止无意义的数据操作。利用触发器可以把符合某些条件的数据加以限制，使其不能变动。

  • 提供审计。利用触发器可以跟踪对数据库的操作，也可以在指定的表或视图记录改变时，利用触发器把数据变动日志记录下来。

  • 允许或限制修改某些表。利用触发器可以限制表的变动。

  • 实现完整性规则。当一个表中的数据有变动时可以利用触发器修改这些变动数据在其他表中的关联数据（正常情况下可以利用外键进行限制）。

  • 保证数据的同步复制。

建议开发人员只在必要时使用触发器，因为触发器可能造成比较杂的相关依赖性， 注意这种情况在大型的数据库中可能会带来麻煩。

1.3 触发器的类型

触发器可分为5种类型，具体内容如下：

  • 数据操纵语言（DML）触发器。

    此种类型触发器定义到表上，当对表执行INSERT、 UPDATE、DELETE操作时可以激发该类型的触发器。利用该类触发器可以复制、检査、 替换某种符合指定条件的数据。

    按照触发级别可以分为两种方式：

    • 第一种为行级触发器，此种类型表示每条记录修改时都会激发该触发器。
    • 第二种为语句级触发器，此种类型表示当SQL语句执行时会激发该触发器，与修改多少条记录没有关系。

    按照数据的更改事件为准，则分为：

    • BEFORE
    • AFTER

  • 数据定义语言（DDL）触发器。

    当CREATE、ALTER、DROP模式对象时会触发相关的触发器，在Oracle中可以简单地理解一个用户就有一个和它同名的模式，利用它可以使得某些表不能被修改或删除。

  • 复合触发器。此种类型的触发器是Oracle 11g的新特性，它相当于在一个触发器中包含 了4种类型的触发器，其中包含了BEFORE类型的语句级、BEFORE类型的行级、 AFTER类型的语句级、AFTER类型的行级。这种把所有触发器都放到一个代码块中的 做法使得变量的传递变得更加方便。

  • INSTEAD OF触发器。此种类型触发器通常作用在视图上。对由多个源表的视图做 DML操作通常是不被允许的，如果遇到这种情况就可以利用INSTEAD OF类型触发器 解决问题。利用它可以把对视图的DML操作转换成对多个源表进行操作。

  • 用户和系统事件触发器。作用在数据库上由数据库事件激发的触发器，如登录和注销 事件的触发器。利用它可以记录数据库的登录情况。

1.4 触发器的语法

(1) DML触发器语法

CREATE [ OR REPLACE ] TRIGGER [schema.] trigger 
    {BEFORE | AFTER | INSTEAD OF}
    {DELETE | INSERT | UPDATE
            [OF column (, column ]...]
    }
    [OR {DELETE | INSERT | UPDATE
          [OF column [,column]...]
        }
    ]...
  {ON [schema. ]table | [schema.] view}
    [FOR EACH ROW ]
    [FOLLOWS [schema.] trigger [, [ schema. ] trigger ]...] 
    [ENABLE | DISABLE]
      [WHEN (condition)]
    trigger_body

【语法说明】

  • OR REPLACE：新建的触发器可以覆盖原有同名触发器。
  • TRIGGER：创建触发器的关键词。
  • schema：触发器所属模式(可简单看成用户名)，如果不加该项则表示该触发器属于自己。
  • BEFORE：触发器类型为前触发。
  • AFTER：触发器类型为后触发。
  • INSTEAD OF：表示触发器类型为替换类型。
  • DELETE I INSERT I UPDATE：表示触发的事件。
  • [ OF column [.column ]：触发条件具体到的某列。
  • ON [ schema. ] table I [ schema. ] view：该触发器作用的表或视图，INSTEAD OF类型可 以作用在视图上。
  • FOR EACH ROW：表示行级触发器，省略则为语句级触发器。
  • FOLLOWS [ schema. ] trigger：触发器执行的顺序。
  • ENABLE I DISABLE：设置触发器是否可用状态。
  • WHEN (condition)：触发该触发器的条件。
  • trigger_body：表示触发器的函数体。

(2) DDL和数据库事件触发器语法

create [or replace] trigger [schema.] trigger
    { BEFORE | AFTER }
    { ddl_event [OR ddl_event]...
    | database_event [OR database_event]...
    }
    
    ON { [schema.] SCHEMA
        | DATABASE
        }
    [FOLLWS [schema.] trigger [,[schema.] trigger ]...]
    [ENABLE | DISABLE]
    [WHEN (condition)]
    trigger_body

【语法说明】

  • OR REPLACE；新建的触发器可以覆盖原有同名触发器。
  • TRIGGER：创建触发器的关键词。
  • schema：触发器所属模式，如果不加该项则表示该触发器属于自己。
  • BEFORE：触发器类型为前触发。
  • AFTER：触发器类型为后触发。
  • ddl.event |OR ddl_event]： DDL事件，用OR连接
  • database_event[OR database_event|:数据库事件，用OR连接。
  • [schema.] SCHEMA I DATABASE：触发器可作用在模式上或数据库上。
  • FOLLOWS [ schema. ] trigger：触发器执行的顺序。
  • ENABLE I DISABLE；设置触发器是否可用状态。
  • WHEN (condition)：触发该触发器的条件。
  • trigger_body：表示触发器的函数体。

部门DDL事件：

DDL事件 简介
ALTER 修改对象，例如修改对象的名称约束等
ANALYSE 用来分析统计信息
AUDIT/NOAUDIT 启用或取消审计
COMMENT 注解列或表的含义
CREATE 创建对象
DROP 删除对象
GRANT 授权操作
RENAME 修改对象名称
REVOKE 取消授权
TRUNCATE 删除整张表的行记录

数据库事件列表：

数据库事件 简介
STARTUP 数据库打开后被触发，模式下不可以
SHUTDOWN 数据库关闭前被触发，模式下不可以
LOGON 客户程序登录后触发
LOGOFF 客户程序注销前触发
SERVERERROR 错误消息出现后触发

(3) 复合触发器语法

CREATE [OR REPLACE] TRIGGER schema.] trigger    
    FOR
    { DELETE | INSERT | UPDATE      
        [OF column [, column ]...]
  }

    [OR {DELETE | INSERT | UPDATE   
            [OF column (,   column]... ]    
        }   
  ]...
 
  ON {(schema.]table            
         | [schema.] view       
     }

    COMPOUND TRIGGER            
    { BEFORE STATEMENT  IS tps_body END BEFORE STATEMENT]   
    | BEFORE EACH ROW   IS tps_body END BEFORE EACH ROW 
    | AFTER STATEMENT   S tps body END AFTER STATEMENT  
    | AFTER EACH ROW    S tps_body END AFTER EACH ROW   
    }

【语法说明】

  • OR REPLACE：新建的触发器可以覆盖原有同名触发器。
  • TRIGGER：创建触发器的关键词。
  • schema：触发器所属模式，如果不加该项则表示该触发器属于自己。
  • DELETE | INSERT | UPDATE：表示触发事件。
  • COMPOUND TRIGGER：定义触发器时表示为复合类型触发器。
  • BEFORE STATEMENT：前语句级触发。
  • BEFORE EACH ROW:前行级触发。
  • AFTER STATEMENT：后语句级触发。
  • AFTER EACH ROW：后行级触发。
  • tps_body:具体语句或程序。

2. 利用SQL*Plus创建触发器

2.1 创建触发器

用户模式下如果想在自己的对象上创建触发器， 则必须具有CREATE TRIGGER系统权限，如果想在其他用户上创建触发器，则需要有CREATE ANY TRIGGER权限。

除此之外，如果在数据库上创建触发器，则需要有ADMINISTER DATABASE TRIGGER系统权限。

🌰：删除emp_temp表时触发

create trigger emp_tri
    --触发类型为后触发，触发事件是删除操作，作用在emp_temp表上
    after delete on emp_temp
    
begin
    if deleting then
        dbms_output.put_line('删除数据操作');
    end if;
end;
/

测试：

SQL> delete from emp_temp where empno = 20;
删除数据操作

已删除 1 行。

2.2 查看触发器

(1) 查看触发器名称

select object_name from user_objects
where object_type = 'TRIGGER';

(2) 查看触发器内容

select * from user_source where name='EMP_TRI' order by line;

2.3 DML类型触发器

dml类型触发器在日常开发中比较常用。

当在 productinfo 表中增加数据时将触发该触发器，并把所做的操作记录到表 option_log 中。

① 创建操作事件记录表

字段名 注释 数据类型
id 记录id，主键 varchar2(10)
oper_table 被操作的表名 varchar2(20)
oper_table_prk 被操作表的主键 varchar2(50)
oper_kd 操作类型 varchar2(10)
oper_date 操作时间 date

SQL：

create table log_tab(
    id varchar2(10),
  oper_table varchar2(20),
  oper_table_prk varchar2(50),
  oper_kd varchar2(10),
  oper_date date
);

② 创建用作 log_tab 表主键的自增长序列

create sequence log_tab_id
    minvalue 1
    maxvalue 999999999
    start with 1
    increment by 1
/

(1) 创建行级触发器

create or replace trigger productinfo_oper_tgr
    --当productinfo表insert之前触发
    before insert on productinfo
        for each row
        
begin
    if inserting then
        insert into log_tab values(log_tab_id.nextval,'productinfo','insert',:new.productid,sysdate);
    end if;
end;
/

行级触发器里使用:new或:old来访问变更前和变更后的数据。其中：

  • 如果增加的是新纪录操作，则只有:new可以访问。
  • 如果是修改操作，则:new或:old都可以访问，:new表示修改后的数据，:old表示修改前的数据。
  • 如果是删除操作，只有:old可以访问，因为该操作是删除已有的记录。

验证触发器：

insert into productinfo values('10','荣耀pad',2000,'平板',100,'郑州','');

会发现，productinfo插入一条数据后，log_tab表中也会插入一条记录。

(2) 多种触发事件

create trigger productinfo_oper_dml_tgr
    after insert or update or delete 
    on productinfo
    for each row

begin
    case
        --增加操作
        when inserting then
            insert into log_tab values(log_tab_id.nextval,'productinfo','insert',:new.productid,sysdate);
            dbms_output.put_line('插入数据完成，主键是：' || :new.productid);
            
        --更新操作
        when updating then
            insert into log_tab values(log_tab_id.nextval,'productinfo','update',:old.productid,sysdate);
            dbms_output.put_line('更新数据完成，主键是：' || :old.productid);
    
    --删除操作
        when deleting then
            insert into log_tab values(log_tab_id.nextval,'productinfo','delete',:old.productid,sysdate);
            dbms_output.put_line('删除数据完成，主键是：' || :old.productid);
            
    end case;
end;
/

验证触发器：

  1. 增加一条数据：

    SQL> insert into productinfo values('11','荣耀10',2400,'手机',80,'台湾','');
插入数据完成，主键是：11

已创建 1 行。

  2. 修改数据：

    SQL> update productinfo set DESCRIPTION = 'test' where productid in (7,8);
更新数据完成，主键是：7
更新数据完成，主键是：8

已更新2行。

  3. 删除数据：

    SQL> delete from productinfo where productid = '11';
删除数据完成，主键是：11

(3) 在触发器中使用if

如果修改的日期是25日，并且修改产品的价格高于3000，那么修改将终止。

create or replace trigger productinfo_oper_chk_tgr
    before update of productprice on productinfo
    for each row
begin
    if (to_char(sysdate,'dd') = 25 and :old.productprice > 3000) then
        raise_application_error(-20000,'今天是25日，不能修改价格高于3000的数据！');
    end if;
    insert into log_tab values (log_tab_id.nextval,'productinfo','insert',:new.productid,sysdate);
    
    dbms_output.put_line('修改数据完成，主键是：' || :new.productid);
end;
/

(4) 使用when限制条件

当在表 productinfo 中增加的数据是”显示器“类型时，需要把当前价格打九折

create or replace trigger productinfo_when_oper_tgr
    before insert on productinfo 
    for each row
    when (new.category = '显示器')
begin
    dbms_output.put_line('原价格：' || :new.productprice);
    :new.productprice := :new.productprice * 0.9;
    dbms_output.put_line('现价格：' || :new.productprice);
end;
/

验证触发器：

SQL> insert into productinfo values('11','荣耀显示器',4599,'显示器',90,'郑州','无');
原价格：4599
现价格：4139.1
插入数据完成，主键是：11

已创建 1 行。

SQL> insert into productinfo values('12','华为matepad',4599,'平板',10,'郑州','无');
插入数据完成，主键是：12

已创建 1 行。

2.4 DDL类型触发器

所谓DDL类型触发器，就是因DDL操作而激发的触发器，主要包括CREATE、ALTER、 DROP等事件

create or replace trigger ddl_tgr
    before create or alter or drop or rename on schema
begin
    if sysevent = 'CREATE' then
        dbms_output.put_line(dictionary_obj_name || '创建中...');
    elsif sysevent = 'DROP' then
        if dictionary_obj_name = 'TEST' then
            raise_application_error(-20000,'不允许删除Test表');
        end if;
    elsif sysevent = 'ALTER' then
        raise_application_error(-20000,'不允许修改表');
    elsif sysevent = 'RENAME' then
        raise_application_error(-20000,'不允许修改表名');
    end if;
end;
/

验证触发器：

SQL> create table test(
  2  id number
  3  );
TEST创建中...

表已创建。


SQL> rename test to test;
rename test to test
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20000: 不允许修改表名
ORA-06512: 在 line 11


SQL> drop table test;
drop table test
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20000: 不允许删除Test表
ORA-06512: 在 line 6

常用事件属性：

属性函数 可用的事件 简介
sysevent 所有事件 返回激发触发器的事件名称
instance_num 所有事件 返回当前数据库的实例号
database_name 所有事件 返回当前的数据库名字
server_error servererror 错误堆栈的置定位置返回错误号
login_user 所有事件 返回激发触发器的用户名
dictionary_obj_type create、alter、drop 返回激活触发器的ddl操作的对象类型
dictionary_obj_name create、alter、drop 返回激活触发器的ddl操作的对象名字

2.5 用户和系统事件触发器

所谓系统事件触发器，就是基于Oracle系统事件而建立的触发器。

该类型的触发器可以审计数据库的登录、注销以及关闭和启动等。

🌰：该示例将记录每个登录用户的时间，并把登录时间存放到用户登录记录表中。

具体步骤如下：

1）创建用户登录日志表

CREATE TABLE LOG_USER (
    LOGONID VARCHAR2(50),
    LOGONNAME VARCHAR2(50),
    LOGONTIME DATE, 
  CONSTRAINT LOG_USER_PRK PRIMARY KEY(LOGONID)
);

2）创建触发器。该触发器是数据库级，记录每个用户的登录时间。具体脚本如下：

CREATE TRIGGER LOGOH_TGR
    AFTER LOGON
    ON DATABASE
BEGIN
    INSERT IOTO LOG_USER
    VALUES(LOG_TAB_ID.NEXTVXL,SYS.LOGIN_PSBR,SYSDATB); 
END;
/

2.6 设置触发器是否可用

触发器被创建后，会不断地被激发，如果业务上不需要使用该触发器了，则可以设置其是 否可用属性，而不必把它删除。
利用 ENABLE | DISABLE关键词设置该触发器是否可用。设置语法如下：

ALTBR TRIGGER [schena.]trigger DISABLE | ENABLE;

🌰：设置DDL_TGR触发器不可用

alter trigger DDL_TGR disable;

2.7 查看触发器状态

select trigger_name,trigger_type,status from user_triggers;

其中 status为ENABLED表示当前触发器启用状态。DISABLED表示当前触发器禁用状态。

3. 修改触发器

修改触发器同样使用 replace 关键字。

在创建触发器时带上or replace关键字，从而完成触发器的修改，也就是覆盖。

4. 删除触发器

语法：

drop trigger [schema.]trigger_name;

🌰：删除触发器 DDL_TGR

drop trigger DDL_TGR;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,445评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,889评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,047评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,760评论 1赞 276
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,745评论 5赞 367
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,638评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,011评论 3赞 398
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,669评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,923评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,655评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,740评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,406评论 4赞 320
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,995评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,961评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,197评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,023评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,483评论 2赞 342

推荐阅读更多精彩内容

  • 第八章 触发器
    触发器是许多关系数据库系统都提供的一项技术。在 ORACLE 系统里，触发器类似过程和函数，都有声明，执行和异常处...
    辽A丶孙悟空阅读 917评论 0赞 14
  • 触发器
    触发器分类 SQL Server提供三类触发器： DML触发器：在数据库中发生数据操作(Insert、Update...
    不知名的蛋挞阅读 1,690评论 0赞 5
  • 《SqlServer编程》15-Sql Server 触发器
    概念：
    BroadZhang阅读 221评论 0赞 1
  • 触发器
    触发器和存储过程比较相似，都是由PL/SQL编写并存储在数据库中的完成某种功能的程序，不同的是存储过程由用户、应用...
    滴滴滴9527阅读 1,249评论 0赞 0
  • 触发器
    概述 触发器是一种特殊类型的存储过程。触发器重要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名被直接调...
    若能遇见阅读 508评论 0赞 0