13-Oracle触发器(定义,管理及测试)
创始人
2024-06-03 06:14:26
0

本章内容

1.触发器概述 2.DML触发器 3.INSTEAD-OF触发器 4.系统触发器 5.变异表触发器 6.触发器的管理

触发器概述

•触发器的概念与作用 •触发器的类型 •触发器组成

(1)触发器的概念与作用

•触发器是一种特殊类型的存储过程,编译后存储在数据库服务器中。 •当特定事件发生时,由系统自动调用执行,而不能由应用程序显式地调用执行。 •触发器不接受任何参数。 •触发器主要用于维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束,并对数据库中特定事件进行监控和响应。

(2)触发器的类型

•DML触发器 建立在基本表上的触发器,响应基本表的INSERT,UPDATE,DELETE操作。 •INSTEAD OF触发器 建立在视图上的触发器,响应视图上的INSERT,UPDATE,DELETE操作。 •系统触发器

          建立在系统或模式上的触发器,响应系统事件和DDL(CREATE,ALTER,DROP)操作。

(3)触发器组成

•触发器由触发器头部和触发器体两个部分组成,主要包括: 作用对象:触发器作用的对象包括表、视图、数据库和模式。 触发事件:激发触发器执行的事件。如DML、DDL、数据库系统事件等。  触发时间:用于指定触发器在触发事件完成之前还是之后执行。如果指定为AFTER,则表示先执行触发事件,然后再执行触发器;如果指定为BEFORE,则表示先执行触发器,然后再执行触发事件。 触发级别:触发级别用于指定触发器响应触发事件的方式。默认为语句级触发器,即触发事件发生后,触发器只执行一次。如果指定为FOR EACH ROW,即为行级触发器,则触发事件每作用于一个记录,触发器就会执行一次。 触发条件:由WHEN子句指定一个逻辑表达式,当触发事件发生,而且WHEN条件为TRUE时,触发器才会执行。 触发操作:触发器执行时所进行的操作。

DML触发器

•DML触发器的种类及执行顺序 •创建DML触发器

(1)DML触发器的种类及执行顺序

•DML触发器的种类 语句级前触发器 语句级后触发器 行级前触发器 行级后触发器

•DML触发器的执行顺序 如果存在,则执行语句级前触发器。 对于受触发事件影响的每一个记录: •如果存在,则执行行级前触发器; •执行当前记录的DML操作(触发事件); •如果存在,则执行行级后触发器。 如果存在,则执行语句级后触发器。

(2)创建DML触发器

•语法
CREATE [OR REPLACE] TRIGGER trigger_name BEFORE|AFTER triggering_event [OF column_name] ON table_name] [FOR EACH ROW] [WHEN trigger_condition] DECLARE      /*Declarative section is here */ BEGIN      /*Exccutable section si here*/ EXCEPTION      /*Exception section is here*/ END [trigger_name];
•语句级触发器 在默认情况下创建的DML触发器为语句级触发器,即触发事件发生后,触发器只执行一次。

•创建一个触发器,禁止在休息日改变雇员信息

CREATE OR REPLACE TRIGGER trg_emp_weekend
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGINIF TO_CHAR(SYSDATE, 'DY', 'nls_date_language= american') IN('SAT', 'SUN')THENraise_application_error(-20000, 'Can''t operate in weekend. ');END IF;
END trg_emp_weekend;

•如果触发器响应多个DML事件,而且需要根据事件的不同进行不同的操作,则可以在触发器体中使用3个条件谓词。

谓词

行为

INSERTING

如果触发语句是INSERT,则为TRUE;

否则为FALSE

UPDATING

如果触发语句是UPDATE,则为TRUE;

否则为FALSE

DELETING

如果触发语句是DELETE,则为TRUE;

否则为FALSE

为emp表创建一个触发器,当执行插入操作时,统计操作后员工人数;当执行更新工资操作时,统计更新后员工平均工资;当执行删除操作时,统计删除后各个部门的人数。

CREATE OR REPLACE TRIGGER trg_emp_dml
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLAREv_count NUMBER;v_sal   NUMBER(6,2);
BEGINIF INSERTING THEN SELECT count(*) INTO v_count FROM emp;DBMS_OUTPUT.PUT_LINE(v_count);ELSIF UPDATING THENSELECT avg(sal) INTO v_sal FROM emp;DBMS_OUTPUT.PUT_LINE(v_sal);ELSEFOR v_dept IN (SELECT deptno,count(*) num FROM emp GROUP BY deptno) LOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.num);END LOOP;END IF;
END trg_emp_dml; 

•行级触发器

行级触发器是指执行DML操作时,每操作一个记录,触发器就执行一次,一个DML操作涉及多少个记录,触发器就执行多少次。 在行级触发器中可以使用WHEN条件,进一步控制触发器的执行。 在行级触发器中引入了:old和:new 两个标识符,来访问和操作当前被处理记录中的数据。 •标识符 :old和:new作为triggering_table%ROWTYPE类型的两个变量 在不同触发事件中,:old和:new的意义不同

触发事件

:old

:new

INSERT

未定义,所有字段都为NULL

当语句完成时,被插入的记录

UPDATE

更新前原始记录

当语句完成时,更新后的记录

DELETE

记录被删除前的原始值

未定义,所有字段都为NULL

•引用方式:

      :old.field和:new.field (执行部分)

       old.field 和new.field   (WHEN条件中)

•注意事项: 是伪记录,不能作为整个记录进行赋值或引用 不能传递给带triggering_table%ROWTYPE参数的过程和函数 如果触发器是建立在嵌套表上,;old和;new都执行嵌套表的行,:parent指向父表中的当前行。

为emp表创建一个触发器,当插入新员工时显示新员工的员工号、员工名;当更新员工工资时,显示修改前后员工工资;当删除员工时,显示被删除的员工号、员工名。

CREATE OR REPLACE TRIGGER trg_emp_dml_row
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGINIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(:new.empno||' '|| :new.ename);ELSIF UPDATING THENDBMS_OUTPUT.PUT_LINE(:old.sal||' '||:new.sal);ELSEDBMS_OUTPUT.PUT_LINE(:old.empno||' '|| :old.ename);END IF;
END trg_emp_dml_row;

•在行级触发器中,可以使用WHEN子句进一步控制触发器的执行。

例如,修改员工工资时,保证修改后的工资高于修改前的工资。

CREATE OR REPLACE TRIGGER trg_emp_update_row
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN(new.sal<=old.sal)
BEGINRAISE_APPLICATION_ERROR(-20001,'The salary is lower!');
END trg_emp_update_row; 

INSTEAD OF触发器

•特点 只能定义在视图上 Instead-of触发器是行级触发器 Instead-of 触发器由DML操作激发,而DML操作本身并不执行 •作用 修改一个本来不可以修改的视图 修改视图中某嵌套表列的列

•如果视图中包含下列任何一项,则该视图不可修改 集合操作符(UNION,UNION ALL,MINUS,INTERSECT); 聚集函数(SUM,AVG等); GROUP BY,CONNECT BY或START WITH子句; DISTINCT操作符; 涉及多个表的连接操作。

•创建INSTEAD OF触发器的基本语法

CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF triggering_event [OF column_name] ON view_name FOR EACH ROW [WHEN trigger_condition] DECLARE       /*Declarative section is here */ BEGIN       /*Exccutable section si here*/ EXCEPTION       /*Exception section is here*/ END [trigger_name];

创建一个包括员工及其所在部门信息的视图empdept,然后向视图中插入一条记录(2345,’TOM’,3000,’SALES’)。

CREATE OR REPLACE VIEW empdept 
AS
SELECT empno,ename,sal,dname 
FROM emp,dept WHERE emp.deptno=dept.deptno  
WITH CHECK OPTION;INSERT INTO empdept
VALUES(2345, 'TOM',3000, 'SALES');
*
ERROR 位于第 1 行:
ORA-01733: 此处不允许虚拟列 
CREATE OR REPLACE TRIGGER trig_view
INSTEAD OF INSERT ON empdept
FOR EACH ROW
DECLAREv_deptno dept.deptno%type;
BEGINSELECT deptno INTO v_deptno FROM dept WHERE dname=:new.dname;INSERT INTO emp(empno,ename,sal,deptno)   VALUES(:new.empno,:new.ename,v_deptno,:new.sal);
END trig_view; 

系统触发器

•触发器事件 •创建系统触发器

(1)触发事件

DDL事件 CREATE,ALTER,DROP,RENAME,

   GRANT,REVOKE,AUDIT,NOAUDIT,

   COMMENT,TRUNCATE,ANALYZE,

   ASSOCIATE STATISTICS,DISASSOCIATE

   STATISTICS等。

触发时间可以是BEFORE,也可以是AFTER。 数据库事件 STARTUP,SHUTDOWN,SERVERERROR,

   LOGON,LOGOFF等。

触发时间由具体事件决定,

数据库事件的触发时间

事件

允许计时

描述

STARTUP

AFTER

当实例开始时激发

SHUTDOWN

BEFORE

当实例关闭时激发

SERVERERROR

AFTER

只要错误发生就激发

LOGON

AFTER

在一个用户成功连接数据库时触发

LOGOFF

BEFORE

在用户注销时开始激发

(2)创建系统触发器

语法 CREATE [OR REPLACE] TRIGGER trigger_name BEFORE|AFTER ddl_event_list|database_event_list ON DATABASE|SCHEMA [WHEN trigger_condition] DECLARE      /*Declarative section is here */ BEGIN     /*Executable section is here*/ EXCEPTION     /*Exception section is here*/ END [trigger_name];
•说明 基于数据库(DATABASE)的触发器,只要系统中该触发事件发生,且满足触发条件,则触发器执行; 对于基于模式(SCHEMAN)的触发器,只有当特定模式中的触发事件发生时,触发器才执行。 •注意 STARTUP和SHUTDOWN事件只能激发基于数据库的触发器。

•将每个用户的登录信息写入temp_table表中。

CREATE OR REPLACE TRIGGER log_user_connection
AFTER LOGON ON DATABASE
BEGININSERT INTO scott.temp_table VALUES (user,sysdate);
END log_user_connection; 
•在系统触发器内部可以使用一些事件属性函数以获得触发事件的信息。 •由于系统没有为这些事件属性函数指定同义词,因此在调用时必须在其前加上“SYS.”前缀。 • 示例 当数据库中执行CREATE操作时,将创建的对象信息记录到ddl_creations表中。

CREATE TABLE ddl_creations (user_id       VARCHAR2(30),object_type    VARCHAR2(20),object_name   VARCHAR2(30),object_owner  VARCHAR2(30),creation_date  DATE);CREATE OR REPLACE TRIGGER log_creations
AFTER CREATE ON DATABASE
BEGININSERT INTO ddl_creationsVALUES(ora_login_user, ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner, sysdate);
END log_creations; 

变异表触发器

概念 变异表是指激发触发器的DML语句所操作的表,即触发器为之定义的表,或者由于DELETE CASCADE操作而需要修改的表,即当前表的子表。 约束表是指由于引用完整性约束而需要从中读取或修改数据的表,即当前表的父表。

•当对一个表创建行级触发器,或创建由DELETE CASCADE操作而激发的语句级触发器时,有下列两条限制: 不能读取或修改任何触发语句的变异表; 不能读取或修改触发表的一个约束表的PRIMARY

   KEY,UNIQUE 或FOREIGN KEY关键字的列, 但

  可以修改其他列。

注意 如果INSERT…VALUES语句只影响一行,那么该语句的行级前触发器不会把触发表当做变异表对待。 INSERT INTO table SELECT…等语句总是把触发表当做变异表,即使子查询仅仅返回一条记录。 •如果既想更新变异表,同时又需要查询变异表,那么如何处理呢? 将行级触发器与语句级触发器结合起来,在行级触发器中获取要修改的记录的信息,存放到一个软件包的全局变量中,然后在语句级后触发器中利用软件包中全局变量信息对变异表的查询,并根据查询的结果进行业务处理。
为了实现在更新员工所在部门或向部门插入新员工时,部门中员工人数不超过8人,可以在emp表上创建两个触发器,同时创建一个共享信息的包。
CREATE OR REPLACE PACKAGE share_pkg
ASv_deptno NUMBER(2); 
END;CREATE OR REPLACE TRIGGER  rmutate_trigger
BEFORE INSERT OR UPDATE OF deptno ON EMP 
FOR EACH ROW
BEGINshare_pkg.v_deptno:=:new.deptno;  
END; 
CREATE OR REPLACE TRIGGER smutate_trigger
AFTER INSERT OR UPDATE OF deptno ON EMP
DECLAREv_num number(3);
BEGINSELECT count(*) INTO v_num FROM emp WHERE deptno=share_pkg.v_deptno;IF v_num>8 THENRAISE_APPLICATION_ERROR(-20003,'TOO MANY EMPLOYEES IN DEPARTMENT '||share_pkg.v_deptno);END IF;
END; 
测试
INSERT INTO emp(empno,ename,sal,deptno) VALUES(2,'WANG',2000,10);
UPDATE emp SET deptno=10 WHERE empno=7844;
UPDATE emp SET deptno=10 WHERE empno=7369;
UPDATE emp SET deptno=10 WHERE empno=7369*
第 1 行出现错误:
ORA-20003: TOO MANY EMPLOYEES IN DEPARTMENT 10
ORA-06512: 在 "SCOTT.SMUTATE_TRIGGER", line 6
ORA-04088: 触发器 'SCOTT.SMUTATE_TRIGGER' 执行过程中出错 

触发器的管理

•触发器名称 触发器存在于单独的名字空间中,在一个模式中可以与其他对象同名 •触发器的限制 不能出现任何事务控制语句。因为触发器作为触发语句执行的一部分,处于同一个事务中。 触发器体所调用的过程或函数都不能发出任何事务控制语句(自治事务子程序除外)。 触发器体中不能声明LONG 或LONG RAW变量,而且:new和:old不能引用LONG或LONG RAW类型的列。 触发器体中可以引用LOB和OBJECT列,但不能修改该列的值。 触发器的大小不能超过32K。 •激活或禁用触发器 可以激活或禁用某个触发器。语法为:

ALTER TRIGGER triggername ENABLE|DISABLE;

激活或禁用某个表对象上的所有触发器。语法为:

ALTER TABLE table_name ENABLE|DISABLE ALL

  TRIGGERS;

•修改触发器 CREATE OR REPLACE TRIGGER trigger_name •重新编译触发器 ALTER TRIGGER trigger_name COMPILE; •查看触发器及其源代码 查询数据字典视图USER_TRIGGERS SELECT trigger_name,trigger_type, table_name,trigger_body FROM user_triggers; •删除触发器 当触发器不再需要时,可以使用DROP TRIGGER语句删除触发器。 DROP TRIGGER trigger_name;

本人从事软件项目开发20多年,2005年开始从事Java工程师系列课程的教学工作,录制50多门精品视频课程,包含java基础,jspweb开发,SSH,SSM,SpringBoot,SpringCloud,人工智能,在线支付等众多商业项目,每门课程都包含有项目实战,上课PPT,及完整的源代码下载,有兴趣的朋友可以看看我的在线课堂

讲师课堂链接:https://edu.csdn.net/lecturer/893

相关内容

热门资讯

模拟安卓系统软件,软件功能与体... 你有没有想过,手机里的世界可以变得更加丰富多彩?没错,就是那种可以像安卓系统一样自由自在地玩耍的世界...
安卓换系统会卡吗,换系统会卡吗... 你有没有想过,你的安卓手机用久了,是不是也会像人一样,反应变得迟钝了呢?没错,这就是我们今天要探讨的...
平板安卓系统自动重启,安卓平板... 你是不是也遇到过这种情况?平板电脑突然间就自动重启了,是不是瞬间感觉心里一紧,心想这可怎么办呢?别急...
findx3安卓系统,安卓系统... 你有没有发现,最近手机圈里又掀起了一股热潮?没错,就是OPPO Find X3系列的安卓系统。这款系...
安卓系统删除的软件,那些曾陪伴... 手机里的软件越来越多,是不是有时候觉得内存不够用,想清理一下呢?别急,今天就来聊聊安卓系统删除软件的...
白色的手机安卓系统,安卓系统下... 你有没有发现,最近市面上那些白色的手机简直让人眼前一亮呢?尤其是搭载安卓系统的那些,简直就像是一块块...
vico是不是安卓系统,揭秘安... 最近是不是有很多小伙伴在问:“Vico手机,它是不是运行安卓系统呢?”这个问题可真是让人好奇啊!今天...
安卓10系统省电不,安卓10系... 你有没有发现,自从升级到安卓10系统,手机续航能力好像大不如前了?别急,今天就来给你揭秘安卓10系统...
cm14安卓系统,深度定制与极... 你有没有发现,你的安卓手机最近是不是有点不一样了?是不是觉得系统运行得更加流畅,界面也更加美观了呢?...
平板安卓系统咋样升级,轻松实现... 你那平板安卓系统是不是有点儿卡,想给它来个升级大变身?别急,让我来给你详细说说平板安卓系统咋样升级,...
安卓原系统在哪下载,探索纯净体... 你有没有想过,为什么安卓手机那么受欢迎?那是因为它的系统——安卓原系统,它就像是一个充满活力的魔法师...
安卓系统procreate绘图... 你有没有发现,现在手机上画画变得越来越流行了?尤其是用安卓系统的手机,搭配上那个神奇的Procrea...
电视的安卓系统吗,探索安卓电视... 你有没有想过,家里的电视是不是也在悄悄地使用安卓系统呢?没错,就是那个我们手机上常用的安卓系统。今天...
苹果手机系统操作安卓,苹果iO... 你有没有发现,身边的朋友换手机的时候,总是对苹果和安卓两大阵营争论不休?今天,咱们就来聊聊这个话题,...
安卓系统换成苹果键盘,键盘切换... 你知道吗?最近我在想,要是把安卓系统的手机换成苹果的键盘,那会是怎样的体验呢?想象那是不是就像是在安...
小米操作系统跟安卓系统,深度解... 亲爱的读者们,你是否曾在手机上看到过“小米操作系统”和“安卓系统”这两个词,然后好奇它们之间有什么区...
miui算是安卓系统吗,深度定... 亲爱的读者,你是否曾在手机上看到过“MIUI”这个词,然后好奇地问自己:“这玩意儿是安卓系统吗?”今...
安卓系统开机启动应用,打造个性... 你有没有发现,每次打开安卓手机,那些应用就像小精灵一样,迫不及待地跳出来和你打招呼?没错,这就是安卓...
小米搭载安卓11系统,畅享智能... 你知道吗?最近小米的新机子可是火得一塌糊涂,而且听说它搭载了安卓11系统,这可真是让人眼前一亮呢!想...
安卓2.35系统软件,功能升级... 你知道吗?最近在安卓系统界,有个小家伙引起了不小的关注,它就是安卓2.35系统软件。这可不是什么新玩...