oracle触发器分类

最新推荐文章于 2023-09-05 13:46:48 发布
最新推荐文章于 2023-09-05 13:46:48 发布
阅读量847 收藏 1
点赞数
分类专栏： 数据库 oracle学习笔记 文章标签： 数据库 dba plsql oracle sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kevin_love_it/article/details/77845273
版权

一.语句级触发器：它是表级触发器，在insert,delete,update,select的DML操作中只会被触发一次

--【1】创建日志表：记录每次向表中更新数据的用户的时间
create table employee_log(
       update_by varchar2(20),
       update_at date
);

select * from employee_log;

--创建t_employee的语句级触发器
create or replace trigger tr_employee_log
before insert or update
on t_employee
begin
 insert into employee_log values(user,sysdate);
end;
--向t_employee表中插入数据触发语句级触发器
insert into t_employee values(11,'李静',6,'ACT');
--查看employe_log
select * from employee_log;
--结果
UPDATE_BY            UPDATE_AT
-------------------- -----------
SCOTT                2017/9/4 22
--修改t_employee表中数据触发语句级触发器
update t_employee set work_year=work_year+1;
--查看employe_log
select * from employee_log;
--结果
UPDATE_BY            UPDATE_AT
-------------------- -----------
SCOTT                2017/9/4 22
SCOTT                2017/9/4 22
综上，无论修改表中多少数据，语句级触发器只会触发一次。

【2】触发器谓词 insertint,deleting,updating

--为employe_log表添加action字段用来记录用户执行了什么操作
alter table employee_log add (action varchar2(10));
--修改tr_employee_log触发器
create or replace trigger tr_employee_log
before insert or update
on t_employee
begin
  if inserting then
     insert into employee_log values(user,sysdate,'INSERT');
  end if;
  if deleting then
     insert into employee_log values(user,sysdate,'DELETE');
  end if;
  if updating then
     insert into employee_log values(user,sysdate,'UPDATE');
  end if;
end;
--向t_employee表中插入数据触发语句级触发器
insert into t_employee values(13,'尼欧',6,'ACT');
--查看employe_log
select * from employee_log;
--结果
UPDATE_BY            UPDATE_AT   ACTION
-------------------- ----------- ----------
SCOTT                2017/9/4 22 
SCOTT                2017/9/4 22 
SCOTT                2017/9/4 23 INSERT
--修改t_employee表中数据触发语句级触发器
update t_employee set work_year=work_year+1;
--查看employe_log
select * from employee_log;
--结果
UPDATE_BY            UPDATE_AT   ACTION
-------------------- ----------- ----------
SCOTT                2017/9/4 22 
SCOTT                2017/9/4 22 
SCOTT                2017/9/4 23 INSERT
SCOTT                2017/9/4 23 UPDATE
综上，日志表会记录t_employee表的更新操作。


--before,after关键字
根据不同的场景，触发器的触发时机不同。
before:在对用户的权限进行控制时，只有有权限的才能执行相应的DML或者DDL,DCL,TCL操作。
after:适合对日志等进行记录，如果用户在执行sql发生了错误，回滚事务，相当于没有实际操作，所以在成功执行后再进行日志记录。

--所以，上面的日志触发器应改为after
create or replace trigger tr_employee_log
after insert or update
on t_employee
begin
  if inserting then
     insert into employee_log values(user,sysdate,'INSERT');
  end if;
  if deleting then
     insert into employee_log values(user,sysdate,'DELETE');
  end if;
  if updating then
     insert into employee_log values(user,sysdate,'UPDATE');
  end if;
end;
二.触发器的级联
注意：对A表操作激活触发器1,触发器1会对B表进行操作，B表上又建立了触发器2,如果触发器2会对A表进行操作，那么就会产生死循环。 

--示例
create table salary_log(
       update_by varchar2(20),
       update_at date,
       action varchar2(20)
);

select * from salary_log;
--------------------------------------
create or replace trigger tr_salary_log
after insert or update
on t_salary
begin
  if inserting then
     insert into salary_log values(user,sysdate,'INSERT');
  end if;
  if deleting then
     insert into salary_log values(user,sysdate,'DELETE');
  end if;
  if updating then
     insert into salary_log values(user,sysdate,'UPDATE');
  end if;
end;
--------------------------------------
create or replace trigger tr_employee_log
before insert or update
on t_employee
begin
  if inserting then
     insert into employee_log values(user,sysdate,'INSERT');
  end if;
  if deleting then
     insert into employee_log values(user,sysdate,'DELETE');
  end if;
  if updating then
     insert into employee_log values(user,sysdate,'UPDATE');
  end if;
  delete from t_salary where
end;
三.行级触发器：它是表级触发器，在insert,delete,update,select的DML操作中只会被触发一次

insert可以使用:new
delete可以使用:old

update可以使用:new和:old

--创建行级触发器
create or replace trigger tr_before_insert_employee
before insert
on t_employee
for each row
begin
  select (max(employee_id)+1) into :new.employee_id from t_employee;
  :new.status:=upper(:new.status);
  :new.work_year:=0;
end;
--测试
insert into t_employee values(0,'行级触发器',10,'act');
--结果
13 行级触发器                    0 ACT
--测试:利用内嵌视图查找插入的数据
select * from (select * from t_employee order by employee_id desc) where rownum=1;
--结果
13 行级触发器                    0 ACT

--示例：使用行触发器记录日志
create table t_employee_history(
       employee_history_id number not null primary key,
       employee_id number not null,
       employee_name varchar2(10),
       work_year number,
       status varchar2(20),
       action varchar2(20),
       update_by varchar2(20),
       update_at date
);

--删除之前t_employee表上的触发器
select * from user_objects where object_type='TRIGGER'
drop trigger tr_salary_log;
drop trigger tr_employee_log;
drop trigger tr_before_insert_employee;
--为t_employee表创建新的触发器
create or replace trigger tr_employee_history
after update or delete
on t_employee
for each row
begin
  declare historyId number;
  begin
    select nvl(max(employee_history_id)+1,0) into historyId from t_employee_history;
    if updating then
      insert into t_employee_history values(historyId,:old.employee_id,:old.employee_name,:old.work_year,'插入数据',:old.status,user,sysdate);
    end if;
    if deleting then
       insert into t_employee_history values(historyId,:old.employee_id,:old.employee_name,:old.work_year,'删除数据',:old.status,user,sysdate);
    end if;
  end;
end;
--测试行级触发器
update t_employee set employee_name='日志触发器' where employee_id=13;
update t_employee set employee_name='日志触发器' where employee_id=12;
update t_employee set employee_name='日志触发器' where employee_id=11;
delete from t_employee where employee_id=13;
--结果
select * from t_employee_history;

E_HISTORY_ID EMPLOYEE_ID EMPLOYEE_NAME  WORK_YEAR STATUS               ACTION               UPDATE_BY            UPDATE_AT
------------------- ----------- ------------- ---------- -------------------- -------------------- -------------------- -----------
                  0          13 行级触发器             0 插入数据             ACT                  SCOTT                2017/9/5 22
                  1          13 日志触发器             0 删除数据             ACT                  SCOTT                2017/9/5 22
                  2          12 李思思                 6 插入数据             XXX                  SCOTT                2017/9/5 22
                  3          11 李静                  11 插入数据             XXX                  SCOTT                2017/9/5 22


触发时机和瞬态，对于行级触发器而言


1.before触发器：插入单条记录会成功，批量插入记录会失败。
原因：因为before触发器，插入单条数据时，表的状态是确定的。如果批量插入数据，那么表的状态就会变成瞬态mutating，
Oracle不允许在表是瞬态的时候去访问触发器关联的表。
2.after触发器：插入单条记录或者批量插入记录都会失败。因为在触发after触发器之前表的状态并没有提交，所以表处于瞬态。

综上所述，如果表处于瞬态则是不允许触发器操作的。


instead of触发器：
语级触发器和行级触发器和触发动作是叠加执行的。
instead of触发器会替代触发语句转而执行触发器操作。

注意：
instead of触发器相当于行级触发器所以不用加for each row.
instead of触发器可以使用访问new引用，但不能更改引用的值。如： :new.salary=:new.salary+1000

--创建视图，统计员工的工资
create or replace view view_salary
as
select e.employee_id,e.employee_name,sum(s.salary) total 
from t_salary s,t_employee e 
where s.employee_id=e.employee_id 
group by e.employee_id,e.employee_name;

--创建instead of触发器替代update操作
create or replace trigger tr_insteadOf_salary
instead of update
on view_salary
  begin
    declare totalMonth number;
    begin
      select count(distinct(month)) into totalMonth from t_salary t where t.employee_id=:old.employee_id;
      update t_salary set salary=salary+(:new.total-:old.total) /totalMonth where employee_id=:old.employee_id;
    end;
  end;
--查看触发器状态
select * from user_objects where object_type='TRIGGER'
--该员工一季度的工资
select * from view_salary where employee_id=10;--15000
----先查看该员工的月工资
select * from t_salary where employee_id=10;  --5000
--测试instead of触发器
update view_salary set total=15000 where employee_id=10;

三.系统事件触发器和用户事件触发器

1.系统事件触发器：数据库启动，数据库关闭，系统错误等

--示例
create or replace trigger tr_database_startup
after startup
on database
begin
  null;
end;

create or replace trigger tr_database_startup
before shutdown
on database
begin
  null;
end;

注意：
startup的话不能写成before，因为数据库未启动是不能存储触发器的
shutdown的话不能写成after，因为数据库未启动是不能存储触发器的
2.用户事件触发器：create,alter,drop,commit,rollback,truncate等,相对于DML操作而言

四.启用和禁用触发器

启用触发器：alter trigger tr_database_startup enable;
关闭触发器：alter trigger tr_database_startup disable;
查看触发器信息
select * from user_objects where object_type='TRIGGER';
select * from user_triggers;

--触发器实例
--创建收入表，支出表，余额表，在其上创建触发器，当收入表insert,delete,update时，余额表进行相应的变化，支出表同理
--收入表income
Name   Type   Nullable Default Comments 
------ ------ -------- ------- -------- 
ID     NUMBER Y                         
INCOME NUMBER Y                收入 
--支出表payout
Name   Type   Nullable Default Comments 
------ ------ -------- ------- -------- 
ID     NUMBER Y                         
PAYOUT NUMBER Y                支出
--余额表balance
Name    Type   Nullable Default Comments 
------- ------ -------- ------- -------- 
BALANCE NUMBER Y                余额 

--为income表创建触发器
create or replace trigger tr_income
after insert or delete or update on income
for each row
begin
  if inserting then
     update balance set balance=balance+:new.income;
  elsif deleting then
     update balance set balance=balance-:new.income;
  elsif updating then
     update balance set balance=balance-:old.income+:new.income;
  end if;
end; 
--为payout表创建触发器
create or replace trigger tr_payout
after insert or delete or update on payout
for each row
begin
  if inserting then
     update balance set balance=balance-:new.payout;
  elsif deleting then
     update balance set balance=balance+:new.payout;
  elsif updating then
     update balance set balance=balance-:old.payout+:new.payout;
  end if;
end; 
--测试tr_income触发器
insert into income values(2,10);
commit;
select * from balance;

kevin_love_it
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle触发器操作.pptx
09-22
Oracle触发器操作.pptx
【四十二、MySql】进阶篇--触发器（介绍、语法、insert/update/delete类型）--总结（视图、存储过程、存储函数、触发器）
qq_49121643的博客
02-21 1985
触发器
Oracle中的触发器
m0_71406734的博客
05-16 1万+
Oracle中的触发器
SQL Server 触发器
TheWindOfFate的博客
11-28 4875
SQL Server 触发器
笔记一：触发器部分
流苏树的博客
12-12 1558
头歌实验难点笔记记录
触发器
cwrygr的博客
08-17 432
一、触发器的工作原理 由于使表中数据发生变化的操作有插入、更新和删除，所以触发器可以分为三类：INSERT触发器、UPDATE触发器和DELETE触发器。 1．INSERT触发器工作过程 当试图向表中插入记录时，INSERT触发器（如果有）自动执行，此时系统自动创建一个inserted表，新的记录被添加到触发器表和inserted表。 触发器可以检查inserted表，确定是否执行触发
Oracle中触发器示例详解
09-09
### 触发器的分类 #### DDL触发器 DDL触发器在执行DDL语句（如GRANT、REVOKE、CREATE、DROP、ALTER、COMMENT、AUDIT、RENAME）时被触发，可以用于限制或记录用户的DDL操作。例如，可以创建一个触发器阻止特定用户...
oracle触发器的设计.pdf
10-08
Oracle触发器是数据库管理系统中的一种特殊类型的存储过程，主要用于在特定的数据库操作（如INSERT、UPDATE或DELETE）发生时自动执行一系列数据库操作。触发器的设计和实现对于数据库的业务规则和完整性约束有着重要...
oracleDML触发器.pptx
09-22
oracleDML触发器.pptx
oracle异常及触发器.pptx
09-22
Oracle 异常及触发器 Oracle 异常是一种运行时错误处理机制，可以捕捉和处理程序执行过程中的错误。异常可以分为预定义异常和用户定义异常两种。 预定义异常是 Oracle 提供的预定义错误类型，例如 Invalid_cursor...
深入理解Oracle触发器的类型与应用（oracle触发器类型）
男神的专栏
05-15 388
Row Level触发器指的是在每个SQL语句在进行INSERT、UPDATE和DELETE操作时，即在数据行的DML操作时执行的触发器，这种类型触发器可以对行操作提供完整的内容控制；System Level触发器指的是在数据库启动期间或关闭期间执行的触发器，它可以响应多种事件，满足客户端特殊的运行要求。总结来说，通过理解Oracle数据库三种触发器的类型，结合相关的实例操作，我们可以更深入地掌握Oracle触发器的应用场景，从而帮助实现自动化数据库管理，实现数据完整性审计和字段约束等功能。
ORACLE 的触发器类型有哪些,ORACLE触发器类型
weixin_29022115的博客
04-07 457
ORACLE触发器类型1.DML触发器由对表的INSERT,DELETE,UPDATE激发CREATE OR REPLACE TRIGGER trigger_nameAFTER -- TIMING :BEFORE OR AFTERDELETE OR INSERT OR UPDATE ON table_name -- STATEM...
SqlServer基础之(触发器)
李赛赛的专栏
08-11 7646
DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用，使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。
insert 触发器
最新发布
xp_zzp的博客
09-05 130
insert 触发器。
数字逻辑复习——触发器
热门推荐
你的博客
12-29 2万+
目录 一、触发器的两个基本特点 二、触发器的分类 (一)按电路结构形式不同划分 (二)按逻辑功能划分 (三)按存储数据的原理不同可分为 三、基本R-S触发器 (一)逻辑功能描述 (二)或非门构成 (三)基本RS触发器的特点 (四)状态转换图 四、同步触发器 (一)同步RS触发器 1、电路结构与工作原理分析 2、设有专门的异步置位输入端和异步复位输入端的同步RS-FF触发器 3、动作特点 (二)同步D触发器 1.逻辑图、特性表和特性方程 2.逻辑功能 (三)同步JK触发器
MySQL触发器
Cdreamfly的博客
06-21 993
  触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）： DELETE； INSERT； UPDATE； 其他MySQL语句不支持触发器。 创建触发器 在创建触发器时，需要给出4条信息： 唯一的触发器名； 触发器关联的表； 触发器应该响应的活动（DELETE、INSERT或UPDATE）； 触发器何时执行（处理之前或之后）。   保持每个数据库的触发器名唯一 在MySQL 5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这表示同
Insert触发器
weixin_30687587的博客
04-23 755
触发器使用的inserted临时表和deleted临时表 1不论后触发或替代触发，每个触发器被激活时，系统都自动为它们创建两个临时表：inserted和deleted表。 2两个表的结构与激活触发器的原数据表结构相同。 3用INSERT语句插入记录激活触发器时，系统在原表插入记录的同时，也自动把记录插入到inserted临时表。 4用SELECT语句删除记录激活触发器时，系统在原表删除记...
数电四：触发器
半个月儿上来的博客
12-23 9980
概 述 数字电路：分组合逻辑电路和时序逻辑电路两大类。 组合逻辑电路的基本单元是门电路。 时序逻辑电路的基本单元是触发器。 一、触发器与门电路的区别 门电路某一时刻的输出信号完全取决于该时刻的输入信号，无记忆功能。 触发器具有记忆功能，能够在无信号的情况下，保持上一次的信号。 二、触发器的现态和次态 现态Qn——触发器接收输入信号之前的状态 次态Qn+1——触发器接收输入信号之后的状态 三、触...
触发器的分类
u013004413的专栏
02-04 631
触发器分四大类  表级触发器，数据库级触发器，全局环境触发器,instead of 触发器 表级触发器:针对单独一个表的触发  Create trigger  AAA on XXX for delete.... 数据库级触发器:针对某一个数据库的触发 create trigger AAA  on XX on database for create_table.... 全局环境触发器:
oracle 触发器
08-02
Oracle触发器是一种在数据库中定义的特殊类型的存储过程，它会在指定的数据库事件发生时自动执行。在Oracle11g和Oracle12c中，可以定义和使用多种类型的触发器。其中包括简单DML触发器（如BEFORE、AFTER和INSERT OF触发器）、组合触发器和非DML触发器（如DDL事件触发器和数据库事件触发器）[1]。 触发器的作用是在特定的数据库事件发生时执行一系列的操作，例如在插入、更新或删除数据时触发某些逻辑。触发器可以用于实现数据完整性约束、审计跟踪、数据变更记录等功能[1]。 创建触发器的语法如下： CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE | {INSERT | UPDATE | DELETE}} ON table_name [FOR EACH ROW] [WHEN (condition)] [DECLARE] -- 声明变量和常量 BEGIN -- 触发器的逻辑代码 END; 触发器可以根据需要定义在表级别或行级别，并可以使用条件谓词来区分不同的触发事件。例如，INSERTING条件谓词在INSERT事件发生时为TRUE，UPDATING条件谓词在UPDATE事件发生时为TRUE，DELETING条件谓词在DELETE事件发生时为TRUE[3]。 总结起来，Oracle触发器是一种在数据库中定义的特殊类型的存储过程，可以在特定的数据库事件发生时自动执行一系列的操作。在Oracle11g和Oracle12c中，可以定义和使用多种类型的触发器，包括简单DML触发器、组合触发器和非DML触发器。触发器可以用于实现数据完整性约束、审计跟踪、数据变更记录等功能[1][2][3]。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值