Oracle19C触发器

2024-09-05 20:12
文章标签 触发器 oracle19c

本文主要是介绍Oracle19C触发器,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

触发器可以看作一种特殊的存储过程,它定义了一些在数据库相关事件(如INSERT、UPDATE、CREATE等事件)发生时应执行的“功能代码块”,通常用于管理复杂的完整性约束,或监控对表的修改,或通知其他程序,甚至可以实现对数据的审计功能。

1. 触发器简介

触发器是通过触发事件来执行的(存储过程的调用或执行是由用户或应用程序进行的)。能够引起触发器运行的操作就被称为触发事件,如执行DML语句(使用INSERT、UPDATE、DELETE语句对表或视图执行数据处理操作)、执行DDL语句(使用CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象)、引发数据库系统事件(如系统启动或退出、产生异常错误等)、引发用户事件(如登录或退出数据库操作)等,以上这些操作都可以引起触发器的运行。
触发器的语法格式:

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;
  • TRIGGER:表示创建触发器的关键字,就如同创建存储过程的关键字PROCEDURE一样。
  • BEFORE | AFTER | INSTEAD OF:表示触发时机的关键字。BEFORE表示在执行DML等操作之前触发,这种方式能够防止某些错误操作发生而便于回滚或实现某些业务规则;
  • AFTER表示在DML等操作之后发生,这种方式便于记录该操作或某些事后处理信息;
  • INSTEAD OF表示触发器为替代触发器。
  • ON:表示操作的数据表、视图、用户模式和数据库等,当对它们执行某种数据操作(如对表执行INSERT、ALTER、DROP等操作)时,将引起触发器的运行。
  • FOR EACH ROW:指定触发器为行级触发器,当DML语句对每一行数据进行操作时都会引起该触发器的运行。如果未指定该条件,则表示创建语句级触发器,这时无论数据操作影响多少行,触发器都只会执行一次。
  • tri_name:触发器的名称,如果数据库中已经存在此名称,则可以通过指定“or replace”关键字将原来的触发器用新的触发器覆盖。
  • tri_event:触发事件,如常用的有INSERT、UPDATE、DELETE、CREATE、ALTER、DROP等。
  • table_name | view_name | user_name | db_name:分别表示操作的数据表、视图、用户模式和数据库,当对它们执行某些操作时,将引起触发器的运行。
  • WHEN tri_condition:这是一个触发条件子句,其中WHEN是关键字,tri_condition表示触发条件表达式。只有当该表达式的值为TRUE时,遇到触发事件才会自动执行触发器,使其执行触发操作,否则即便是遇到触发事件也不会执行触发器。
  • plsql_sentences:PL/SQL语句,它是触发器功能实现的主体。

Oracle支持的触发器分为以下5种类型:

  • 行级触发器:当DML语句对每一行数据进行操作时都会引起该触发器的运行。
  • 语句级触发器:无论DML语句影响多少行数据,其所引起的触发器仅执行一次。
  • 替换触发器:该触发器是定义在视图上的,而不是定义在表上,它是用来替换所使用实际语句的触发器。
  • 用户事件触发器:是指与DDL操作或用户登录、退出数据库等事件相关的触发器,如用户登录到数据库或使用ALTER语句修改表结构等事件的触发器。
  • 系统事件触发器:是指在Oracle数据库系统的事件中进行触发的触发器,如Oracle实例的启动与关闭。

2. 语句级触发器

语句级触发器,是针对一条DML语句引起的触发器执行。在语句级触发器中,不使用FOR EACH ROW子句,也就是说无论数据操作影响多少行,触发器都只会执行一次。使用触发器在scott模式下针对dept表的各种操作进行监控,为此首先需要创建一个日志表dept_log,它用于存储对dept表的各种数据操作信息,如操作种类(如插入、修改、删除操作)、操作时间等,在scott模式下创建dept_log数据表,并在其中定义两个字段,分别用来存储操作种类信息和操作日期,
代码如下:

create table dept_log
(operate_tag varchar2(10),  --定义字段,存储操作种类信息operate_time date          --定义字段,存储操作日期
);

创建一个触发器tri_dept,该触发器在INSERT、UPDATE和DELETE事件下都可以被触发,操作的数据对象是dept表,并且在触发器执行时输出对dept表所做的具体操作,代码如下:

create or replace trigger tri_deptbefore insert or update or deleteon dept   --创建触发器,当dept表发生插入、修改、删除操作时,将引起该触发器执行
declarevar_tag varchar2(10);    --声明一个变量,存储对dept表执行的操作类型
beginif inserting then      --当触发事件是INSERT时var_tag := '插入';    --标识插入操作elsif updating then    --当触发事件是UPDATE时var_tag := '修改';    --标识修改操作elsif deleting then    --当触发事件是DELETE时var_tag := '删除';    --标识删除操作end if;insert into dept_logvalues(var_tag,sysdate); --向日志表中插入对dept表的操作信息
end tri_dept;
/

在上述代码中,使用BEFORE关键字来指定触发器的“触发时机”,它指定当前的触发器在DML语句执行之前被触发,这使得它非常适合于强化安全性、启用业务逻辑和进行日志信息记录。当然也可以使用AFTER关键字,它通常被用于记录该操作或者做某些事后处理工作。具体使用哪一种关键字,要根据实际需要而定。在数据表dept中实现插入、修改、删除3种操作,以便引起触发器tri_dept的执行,
代码如下:

insert into dept values(55,'业务部','上海');
update dept set loc='杭州' where deptno=55;
delete from dept where deptno=55;

上述代码对dept表执行了3次DML操作,这样根据tri_dept触发器自身的设计情况,其会被触发3次,并且会向dept_log表中插入3条操作记录。

3. 行级触发器

行级触发器会针对DML操作所影响的每一行数据都执行一次触发器,创建这种触发器时,必须在语法中使用FOR EACH ROW这个选项;
使用行级触发器的一个典型应用就是给数据表生成主键值。为了使用行级触发器生成数据表中的主键值,首先需要创建一个带有主键列的数据表。
在scott模式下,创建一个用于存储商品种类的数据表,其中包括商品序号列和商品名称列,

代码如下;

create table goods
(id int primary key,  --设置id为主键good_name varchar2(50)
);

为了给goods表的id列生成不能重复的有序值,这里需要创建一个序列,使用CREATE SEQUENCE语句创建一个序列,命名为seq_id,
代码如下:

create sequence seq_id;

上述代码创建了序列seq_id,用户可以在PL/SQL程序中调用它的NEXTVAL属性来获取一系列有序的数值,这些数值就可以被作为goods表的主键值。
创建一个行级触发器,该触发器在向数据表goods中插入数据时被触发,并且在该触发器的主体中实现设置goods表中的id列的值,
代码如下:

create or replace trigger tri_insert_goodbefore inserton goods       --关于goods数据表,在向其插入新记录之前,引起该触发器的运行for each row   --创建行级触发器
beginselect seq_id.nextvalinto :new.idfrom dual;    --从序列中生成一个新的数值,赋值给当前插入行的id列

在上述代码中,为了创建行级的触发器,使用了FOR EACH ROW选项;为了给goods表中的当前插入行的id列赋值,这里使用了:new.id关键字—也被称为“列标识符”,这个列标识符用来指向新行的id列,给它赋值,就相当于给当前行的id列赋值。

在行级触发器中,可以通过“列标识符”来访问当前正在受到影响(添加、删除、修改等操作)的数据行,列标识符可以分为“原值标识符”和“新值标识符”。原值标识符用于标识当前行的某个列的原始值,记作:old.column_name(如:old.id),通常在UPDATE语句和DELETE语句中被使用,因为在INSERT语句中新插入的行没有原始值;新值标识符用于标识当前行的某个列的新值,记作:new.column_name(如:new.id),通常在INSERT语句和UPDATE语句中被使用,因为在DELETE语句中被删除的行无法产生新值。在触发器创建完毕之后,可以通过向goods表中插入数据来验证触发器是否被执行,同时能够验证该行级触发器是否能够使用序列为表的主键赋值。

向goods表中插入两条记录,其中一条记录不指定id列的值,由序列seq_id来产生;另一条记录指定id的值,
代码如下:

insert into goods(good_name) values('香蕉');
insert into goods(id,good_name) values(9,'柚子');

可以看到,无论是否指定id列的值,数据的插入都是成功的,而且即使第二次插入数据指定了id的值为9,也没有起作用,这是因为在触发器中将序列seq_id的NEXTVAL属性值赋给了:new.id列标识符,这个列标识符的值就是当前插入行的id列的值,并且NEXTVAL属性值是连续不间断的。

4. 替换触发器

替换触发器即INSTEAD OF触发器,它的“触发时机”关键字是INSTEAD OF,而不是BEFORE或AFTER。与其他类型触发器不同的是,替换触发器是定义在视图上的,而不是定义在表上。由于视图是由多张基表连接组成的逻辑结构,因此一般不允许进行DML操作(如INSERT、UPDATE、DELETE等操作),当为视图编写替换触发器后,对视图的DML操作实际上就变成了执行触发器中的PL/SQL块,这样就可以通过在替换触发器中编写适当的代码对构成视图的各张基表进行操作。
在scott模式下创建一个检索员工信息的视图,该视图的基表包括dept表(部门表)和emp表(员工表),
代码如下:

--创建视图 view_emp_dept
create view view_emp_deptas select empno,ename,dept.deptno,dname,job,hiredatefrom emp,deptwhere emp.deptno = dept.deptno;

尝试向view_emp_dept视图中插入数据,会提示“ORA-01776: 无法通过联接视图修改多个基表”;创建一个关于view_emp_dept视图的替换触发器,在该触发器的主体中实现向emp表和dept表中插入两行相互关联的数据,
代码如下:

create or replace trigger tri_insert_viewinstead of inserton view_emp_dept    --创建一个关于view_emp_dept视图的替换触发器for each row        --是行级视图
declarerow_dept number;
beginSELECT count(*) INTO row_dept FROM dept WHERE deptno = :new.deptno;--检索指定部门编号的记录行  if row_dept = 0 then     --判断是否存在该部门编号的记录insert into dept(deptno,dname)values(:new.deptno,:new.dname);   --向dept表中插入数据end if;insert into emp(empno,ename,deptno,job,hiredate)values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);  --向emp表中插入数据
end tri_insert_view;
/

在上述触发器的主体代码中,如果新插入行的部门编号(deptno)不在dept表中,则首先向dept表中插入关于新部门编号的数据行,然后再向emp表中插入记录行,这是因为emp表的外键值(emp.deptno)是dept表的主键值(dept.deptno)。
成功创建触发器tri_insert_view之后,在向view_emp_dept视图中插入数据时,Oracle就不会产生错误信息,而是引起触发器tri_insert_view的运行,从而实现向emp表和dept表中插入两行数据。
向视图view_emp_dept中插入一条记录,然后检索插入的记录行,
代码如下:

--向视图插入一条数据
insert into view_emp_dept (empno,ename,deptno,dname,job,hiredate) values (7777,'小明',99,'测试部','测试工程师',sysdate);--查询deptno=99的数据
select * from dept where deptno=99;
--查询empno=7777的数据
select * from emp where empno=7777;

dept表之前是没有部门编码deptno=99的记录的,触发器中的程序向dept表中插入deptno=99的数据,然后又向emp表中插入一条记录。

5. 用户事件触发器

用户事件触发器是因进行DDL操作或用户登录、退出等操作而引起运行的一种触发器,引起该类型触发器运行的常见用户事件包括CREATE、ALTER、DROP、ANALYZE、COMMENT、GRANT、REVOKE、RENAME、TRUNCATE、SUSPEND、LOGON和LOGOFF等。
使用CREATE TABLE语句创建一个日志信息表,该表保存的日志信息包括数据对象、数据对象类型、操作行为、操作用户和操作日期等,
代码如下:

create table ddl_oper_log
(db_obj_name varchar2(20),  --数据对象名称db_obj_type varchar2(20),  --对象类型oper_action varchar2(20),  --具体ddl行为oper_user varchar2(20),    --操作用户oper_date date             --操作日期
);
创建一个关于scott用户的DDL操作(这里包括CREATE、ALTER和DROP)的触发器,然后将DDL操作的相关信息插入ddl_oper_log日志表中,代码如:create or replace trigger tri_ddl_operbefore create or alter or dropon scott.schema    --在scott模式下,在创建、修改、删除数据对象之前将引发该触发器运行
begininsert into ddl_oper_log values(ora_dict_obj_name,     --操作的数据对象名称ora_dict_obj_type,     --对象类型ora_sysevent,          --系统事件名称ora_login_user,        --登录用户sysdate);
end;
/

当向日志表ddl_oper_log中插入数据时,使用了若干个事件属性,
其含义如下:

ora_dict_obj_name:获取DDL操作所对应的数据库对象。
ora_dict_obj_type:获取DDL操作所对应的数据库对象的类型。
ora_sysevent:获取触发器的系统事件名。
ora_login_user:获取登录用户名。

通过上述4个事件属性值和sysdate系统属性就可以将scott用户的DDL操作信息获取出来,然后再把这些信息保存到ddl_oper_log日志表中。在scott模式下,首先创建一张数据表和一个视图,然后删除视图和修改数据表,最后使用SELECT语句查看ddl_oper_log日志表中的DDL操作信息,
代码如下:

--创建tb_test表
create table tb_test(id number);
--创建view_test视图
create view view_test as select empno,ename from emp;
--修改tb_test表
alter table tb_test add(name varchar2(10));
--删除视图view_test
drop view view_test;
--查询ddl_oper_log表
select * from ddl_oper_log;

可以看到,用户scott的DDL操作信息都被存储到ddl_oper_log日志表中,这些信息就是由DDL操作引起触发器运行而保存到日志表中的。

6. 删除触发器

当一个触发器不再使用时,要从内存中删除它,例如:–删除名为tri_dept的触发器

DROP TRIGGER tri_dept;

当一个触发器已经过时,想重新定义时,不必先删除再创建,只需在CREATE语句后面加上OR REPLACE关键字即可,
代码如下:

CREATE OR REPLACE TRIGGER my_trigger;

这篇关于Oracle19C触发器的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1139946

相关文章

Oracle数据库(触发器、存储过程、函数、包)

一、触发器(trigger) 推荐练习文档: http://wenku.baidu.com/link?url=brtd9myO4XIaEjRPaRB0yWgV2_TfXmNxS6KKjB_avq7d0hPs3SknJdrs-JLtWe–H-f3q-I61HUxkcqqAFMC0z6Zf65QBbmFRB8TAlULmGS http://www.cnblogs.com/friends

Flink实例(115):自定义时间和窗口的操作符(十四)窗口操作符(四)触发器(Triggers) (二)

Flink 中窗口是很重要的一个功能,而窗口又经常配合触发器一起使用。 Flink 自带的触发器大概有: CountTrigger: 指定条数触发ContinuousEventTimeTrigger:指定事件时间触发ContinuousProcessingTimeTrigger:指定处理时间触发ProcessingTimeTrigger: 默认触发器,窗口结束触发EventTime

SQL server触发器简单示例

一:触发器是一种特殊的存储过程,它不能被显式地调用,而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。   二:SQL Server为每个触发器都创建了两个专用表:Inserted表和Deleted表。这两个表由系统来维护,它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成

oracle触发器浅析

1.定义:数据库触发器是一个与表相关联的,存储的pl/sql程序 2.作用:每当一个特定的数据操作数据(insert,update,delete)在指定的表上操作时,oracle自动滴执行触发器中的语句序列 3.语法:create [or replace] trigger 触发器名before/After(执行前/后出发) insert(执行操作insert,update,delete[of

Quartz.Net_侦听触发器

简述 触发器执行任务时存在数个阶段,利用侦听器可以在对应的阶段执行一些代码 如何侦听 1.实现ITriggerListener接口,并实现其函数,具体说明见代码注释 public class DeleteAfterCompleted : ITriggerListener{public string Name => "X";// 触发时public async Task TriggerFi

SQL Server触发器的创建

参考http://blog.csdn.net/fredrickhu/article/details/4708906一 (一)先打开sql server数据库,创建数据库Stu ,创建两个表: create database Stu create table Student(StudentID int primary key,StudentName nvarchar(50)

Oracle—触发器(转)

触发器的种类和触发事件 触发器必须由事件才能触发。触发器的触发事件分可为3类,分别是DML事件、DDL事件和数据库事件。 每类事件包含若干个事件,如下所示。数据库的事件是具体的,在创建触发器时要指明触发的事件。 种 类 关 键 字 含 义: DML事件是针对数据,DDL事件是针对对象(表,视图,触发器,存储过程等等),数据库事件针对数据库服务。 DML事件(3种)

008 数据库对象—触发器—讲解

触发器看的我一知半解,看到师兄的一篇触发器的博客特别好,看完以后觉得茅塞顿开,我这个闷葫芦终于也开窍了!先跟大家分享一下这犀利的好文章,其后是我个人的一下补充和思维导图。 分享: 触发器简介: 触发器(trigger)是种特殊的存储过程,它的执行不是由程序调用,也不需要手动操作,它是由事件来触发,事件大家应该非常熟悉吧,比如按钮的Click事件、网页的Load事件等。按钮的Click

数据库里面的updateTimed选择代码更新和还是数据库触发器自动更新?

1.updateTime更新可以归纳以下几种: 在项目中,当对数据库的数据进行修改时,updateTime(或类似的表示最后更新时间的字段)的更新时机通常取决于你的应用程序逻辑或使用的数据库框架(如ORM)的行为。 手动更新:最直接的方式是在你的代码中,每次对数据库中的记录进行修改时,显式地更新updateTime字段。这通常涉及到在更新记录的SQL语句中包括updateTime字段,并设置其

Unity3D C# 定时触发器 定时任务 大型RPC游戏

支付宝捐赠 注释很详细,具体使用方法看代码注释 // Test//定时器显示var timerid = TimerHeap.AddTimer(0, 500, () =&