Oracle 触发器详解(trigger)

Oracle 触发器详解(trigger)文章目录1概述2触发器管理2.1创建触发器2.1.1foreachrow2.1.2follows2.1.3when2.2查询触发器2.3删除触发器2.4常用属性2.4.1inserting、updating、deleting2.4.2now、old3触发器分类3.1DML触发器3.1.1单列触发:of列名3.2DDL触发器3.3Databse触发器3.4insteadof替换触发器1概述1.触发器是什么..

文章目录

1 概述

1. 触发器是什么?
   当 '触发条件' 成立时,其语句就会 '自动执行'   
2. 触发器有什么用?
   保护数据的安全,监视对数据的各种操作,如
   '日志记录': 对重要表的 '修改' 进行记录
3. 触发器和存储过程的区别?
   主要区别:'调用运行方式不同'
   (1) 存储过程: '用户''应用程序''触发器' 来调用
   (2) 触发器:   '自动执行'(满足 '触发条件'),与其它无关

 
 
 

2 触发器管理

2.1 创建触发器

create [or replace] trigger 触发器名
   触发时间 {before | after}           -- view 中是 instead of
   触发事件 {
  
  insert | update | delete} -- dml、ddl、database
on 触发对象                            -- table、view、schema、database 
   触发频率 {
  
  for each row}            -- 行级触发器。默认:语句级触发器
   [follows 其它触发器名]              -- 多个触发器执行的 前后顺序
   [when 触发条件]
begin
   pl/sql 语句;
end;

 
 
 

关键字说明:

1. 触发器名:一般格式 tr_*
2. 触发时间:在 '触发事件' 发生之前(before)还是之后(after3. 触发事件:根据不同的 '触发事件',可以分为不同的 '类型'
4. 触发对象:tableviewschemadatabase
5. 触发频率:'语句级触发器'(默认)指触发一次,'行级触发器' 每一行触发一次
6. 触发条件:仅当 '触发条件'True 时,才执行 pl/sql 语句

 
 
 

基础数据准备:

create table scott.student_info (
  sno   number(10),
  name  varchar2(30),
  sex   varchar2(2)
);
insert into scott.student_info(sno, name, sex) values(1, '张三', '女');
insert into scott.student_info(sno, name, sex) values(2, '李四', '男');
insert into scott.student_info(sno, name, sex) values(3, '王五', '女');
commit;

 
 
 

特别提醒:在演示某触发器效果时,建议先删除其它触发器,避免影响测试结果

2.1.1 for each row

/* 功能:after insert or update or delete 时,执行语句 命名:tr_aiud_student_info */
create or replace trigger scott.tr_aiud_student_info
   after insert or update or delete on scott.student_info
   for each row
begin
   case
      when inserting then
         dbms_output.put_line('插入成功!');
      when updating then
         dbms_output.put_line('更新成功!');
      when deleting then
         dbms_output.put_line('删除成功!');
      else
         dbms_output.put_line('无操作!');
   end case;
end;
/

 
 
 

测试语句:

update scott.student_info t
   set t.sex = '1'
 where t.sno <= 3;

 
 
 

PL/SQL 输出窗口:3 条记录,故共触发 3 次

更新成功!
更新成功!
更新成功!

 
 
 

提示:若去掉 for each row,再执行上述操作,则仅触发 1

2.1.2 follows

前提:触发器的执行是否需要指定 '先后顺序'1. 若不需要,则无需 follows 关键字
2. 若需要
   (1) before 和 after 能否区分,若能,则无需 follows 关键字
   (2) 最后,才用 follows 区分

 
 
 

请注意:测试前,先删除所有触发器,避免影响测试结果

select 'DROP TRIGGER ' || t.owner || '.' || t.trigger_name || ';' 删除
  from all_triggers t
 where t.owner = 'SCOTT'
   and t.table_name = 'STUDENT_INFO';

 
 
 

触发器1:

create or replace trigger scott.tr_ai_student_info_1
   after insert on scott.student_info
   for each row
begin
   if inserting then
      dbms_output.put_line('插入操作 1');
   end if;
end;
/

 
 
 

触发器2:(顺序:先触发器1,再触发器2)

create or replace trigger scott.tr_ai_student_info_2
   after insert on scott.student_info
   for each ROW
   FOLLOWS scott.tr_ai_student_info_1
begin
   if inserting then
      dbms_output.put_line('插入操作 2');
   end if;
end;
/

 
 
 

测试语句:

insert into scott.student_info(sno, name, sex) values(5, '赵六', '女');

 
 
 

PL/SQL 输出窗口:

插入操作 1
插入操作 2

 
 
 

2.1.3 when

1. when:增加触发条件
2. when 中的 new、old 是不带 : 的哦(不是 :new、:old)

 
 
 
create or replace trigger scott.tr_ad_student_info
   after delete on scott.student_info
   for each row
   when (old.sno = 1) -- sno = 1 的记录禁止被删除!
begin
   if deleting then
      raise_application_error(-20001, '此条记录禁止删除,sno = ' || :old.sno);
   end if;
end;
/

 
 
 

测试语句:

delete from scott.student_info t where t.sno = 1;

 
 
 

测试结果:弹框 – 错误提醒

2.2 查询触发器

权限范围,由大到小:dba_* > all_* > user_*
select * from dba_triggers;
select * from all_triggers;
select * from user_triggers;

 
 
 

2.3 删除触发器

drop trigger 触发器名;
select 'DROP TRIGGER ' || t.owner || '.' || t.trigger_name || ';' 删除
  from all_triggers t
 where t.owner = 'SCOTT'
   and t.table_name = 'STUDENT_INFO';

 
 
 

2.4 常用属性

2.4.1 inserting、updating、deleting

1. 前提条件:无 
2. 表示含义
   inserting = insert 操作
   updating  = update 操作
   deleting  = delete 操作

 
 
 

2.4.2 now、old

1. 前提条件:for each row
2. 表示含义
   :new = 触发前的值
   :old = 触发后的值
3. 说明 
   (1) new、old 均为 '默认值', 常用, 一般无需更改
       referencing new as new old as old       
   (2) 若想要更改,如:new => new_new,old => old_old  
       referencing new as new_new old as old_old

 
 
 

:new、:old 值分布情况:

insert update delete
:new ×
:old ×
create or replace trigger scott.tr_au_student_info
   after update on scott.student_info
   for each row
begin
   -- 旧值
   dbms_output.put_line('old.sno = ' || :old.sno);
   dbms_output.put_line('old.name = ' || :old.name);
   dbms_output.put_line('old.sex = ' || :old.sex);
   dbms_output.put_line('------');
   -- 新值
   dbms_output.put_line('new.sno = ' || :new.sno);
   dbms_output.put_line('new.name = ' || :new.name);
   dbms_output.put_line('new.sex = ' || :new.sex);
end;
/

 
 
 

测试语句:

update scott.student_info t
   set t.name = 'name',
       t.sex = '2'
 where t.sno = 1;

 
 
 

测试结果:PL/SQL 输出窗口

old.sno = 1
old.name = 张三
old.sex =------
new.sno = 1
new.name = name
new.sex = 2

 
 
 

3 触发器分类

3.1 DML 触发器

同上述案例,触发事件:insertupdatedelete

 
 
 

3.1.1 单列触发:of 列名

1. 上述案例中,均是记录 '所有列' 的变化,若只想关注其中 '几列' 的变化,该如何呢?
2. 此时就用到 'of 列名' 子句,多个列用逗号 ',' 隔开即可

 
 
 
create or replace trigger scott.tr_au_student_info
  after update of sno, name on scott.student_info
  for each row
begin
  if :new.sno <> :old.sno or :new.name <> :old.name then
    raise_application_error(-20001,
                            '禁止操作!修改 sno = ' || :new.sno || ', name = ' ||
                            :new.name);
  end if;
end;

 
 
 

测试语句:

update scott.student_info t
   set t.name = '哈哈'
 where t.sno = 1;

 
 
 

测试结果:弹框 – 错误提醒

3.2 DDL 触发器

触发事件:createalterdrop

 
 
 
  • 1
-- **********************************************************************
-- 功能:非 DBA 管理员禁止操作, 如:wangyou
-- 限定符合下列 类型 和 域账户 的人,才能操作 create、alter、drop
-- **********************************************************************
create or replace trigger scott.tr_dba_control
  before create or alter or drop on database
declare
  v_user_name   varchar2(50); -- 用户名
begin
  select sys_context('USERENV', 'OS_USER') into v_user_name from dual;
  if dbms_standard.dictionary_obj_type in
     ('TABLE', 'SYNONYMS', 'USER', 'TABLESPACE') and
     v_user_name not in ('wangyou') then
    raise_application_error(-20000,
                            v_user_name || '用户无 DDL-' || ora_sysevent ||
                            ' 权限,请联系数据架构设计处处理!');
  end if;
end;

 
 
 

3.3 Databse 触发器

触发事件
startup:'数据库打开'时,相反的 = shutdown
logon  :当用户连接到数据库并 '建立会话' 时,相反的 = logoff
servererror:发生服务器错误时

 
 
 
create table scott.database_login_info (
   client_ip      varchar2(30),
   login_user     varchar2(30),
   database_name  varchar2(30),
   database_event varchar2(30),
   create_user    varchar2(50),
   create_data    date
);

 
 
 
create or replace trigger scott.tr_al_database_login_info
  after logon on database
declare
  v_option_user varchar2(50) := sys_context('USERENV', 'OS_USER'); -- 电脑域账户
begin
  insert into scott.database_login_info
    (client_ip,
     login_user,
     database_name,
     database_event,
     create_user,
     create_data)
  values
    (dbms_standard.client_ip_address,
     dbms_standard.login_user,
     dbms_standard.database_name,
     dbms_standard.sysevent,
     v_option_user,
     sysdate);
end;

 
 
 

3.4 instead of 替换触发器

1. 只适用于视图(多个简单的基表相连),不能直接作用于表上(间接)
2. 很少使用,个人感觉,不如 dml 触发器来得直观
3. 必须包含 for each row 选项

 
 
 
create or replace trigger <触发器名称>
   instead of insert or update or delete on <视图名>
   for each row -- 必填,且唯一
begin
   pl/sql 语句;
end;

 
 
 

今天的文章Oracle 触发器详解(trigger)分享到此就结束了,感谢您的阅读。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/5420.html

(0)
编程小号编程小号

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注