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

相关内容

热门资讯

将安卓系统数据导入ios系统,... 你是不是也有过这样的经历:手机里存满了珍贵的照片、联系人、应用数据,突然有一天,你决定换一台iPho...
雷霆战机 安卓最低系统,系统要... 你有没有听说过这款超级酷炫的飞行游戏——雷霆战机?没错,就是那个让你一上手就停不下来的刺激游戏!今天...
电脑独立安装安卓系统,探索电脑... 电脑独立安装安卓系统:探索未来的可能性在当今这个数字化时代,电脑已经不仅仅是一个用来打字的工具,它更...
树莓派虚拟安卓系统,打造便携式... 你有没有想过,用树莓派来运行安卓系统?听起来是不是有点酷炫?想象一个迷你的小树莓派,竟然能变身成为一...
安卓系统如何安装tk,安卓系统... 你有没有想过,你的安卓手机里装个tk,生活是不是能变得更有趣呢?别急,别急,我来给你详细说说怎么操作...
安卓计步数系统,健康生活新伙伴 你有没有发现,每天手机里那个默默无闻的安卓计步数系统,竟然悄悄地记录了你走过的每一步?今天,就让我带...
安卓原生系统真的好吗,究竟是否... 你有没有想过,安卓原生系统到底是不是那个传说中的“好”?咱们今天就来聊聊这个话题,看看这个系统到底有...
安卓2.3系统开机画面,复古与... 亲爱的读者,你是否还记得那些充满怀旧情怀的安卓2.3系统开机画面?那个曾经陪伴我们度过无数时光的小图...
哪个安卓管理系统好,安卓管理系... 你有没有想过,手机里那么多应用,管理起来是不是有点头疼?别急,今天就来给你揭秘哪个安卓管理系统好,让...
安卓系统取消流量监控,告别流量... 你知道吗?最近安卓系统来了一次大动作,取消了对流量的监控!这可真是让人眼前一亮的消息呢。想象以前每次...
安卓导航如何备份系统,安卓导航... 你有没有想过,如果你的安卓导航系统突然崩溃了,你会怎么办?别急,今天就来给你支个招,教你如何轻松备份...
小米安卓4.0系统下载,深度解... 你有没有想过,你的小米手机是不是也能来个“大变身”?没错,就是升级到安卓4.0系统!想象你的手机瞬间...
自动鉴别平板安卓系统,自动鉴别... 你有没有想过,你的平板电脑里那些安卓系统,其实都是经过一番“智慧”的筛选和鉴别才来到你面前的呢?没错...
bose能连安卓系统,开启无线... 你有没有想过,家里的音响设备也能跟上科技潮流,和你的安卓手机无缝连接呢?没错,今天就要来聊聊这个神奇...
安卓x是什么系统,探索新一代智... 你有没有听说最近安卓界又出了个新花样——安卓X系统?没错,就是那个我们平时手机里常用的安卓系统,这次...
安卓系统怎么提升网速,五大技巧... 你是不是也和我一样,在使用安卓手机时,总是觉得网速不够快,有时候刷个网页都慢得让人抓狂?别急,今天就...
机车安卓系统重装,轻松恢复系统... 你那台机车安卓系统是不是突然间卡壳了,运行速度慢得像蜗牛爬?别急,今天就来给你详细说说怎么给机车安卓...
华为手机更改安卓系统,探索安卓... 你知道吗?最近华为手机界可是掀起了一阵不小的波澜呢!没错,就是那个我们熟悉的华为,竟然悄悄地更改了安...
安卓系统低版微信,揭秘微信新版... 你有没有发现,有时候手机里的微信版本有点儿“老气横秋”呢?别急,今天就来聊聊这个让人有点头疼的安卓系...
安卓系统安装apple pay... 你有没有想过,即使你的手机是安卓系统,也能享受到Apple Pay的便捷支付体验呢?没错,就是那个曾...