search
HomeDatabaseMysql TutorialOracle PL/SQL 触发器(trigger)学习笔记

触发器也是一种带名的PL/SQL块。触发器类似于过程和函数,因为它们都是拥有声明、执行和异常处理过程的带名PL/SQL块。与包类似,

1、触发器的概念

触发器也是一种带名的PL/SQL块。触发器类似于过程和函数,因为它们都是拥有声明、执行和异常处理过程的带名PL/SQL块。与包类似,触发器必须存储在数据库中并且不能被块进行本地化声明。

对于触发器而言,当触发事件发生的时候就会显式地执行该触发器,并且触发器不接受参数。

创建触发器的语法如下

view plaincopy to clipboardprint?

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF} triggering_event

[referencing_clause]

[WHEN trigger_condition]

[FOR EACH ROW]

Trigger_body;

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF} triggering_event

[referencing_clause]

[WHEN trigger_condition]

[FOR EACH ROW]

Trigger_body;

其中referencing_clause子句的用途是通过一个不同的名称,引用当前正在被更新的记录行中的数据。WHEN子句中的trigger_condition—如果出现—就应该首先执行判断,只有当这个条件值为真的时候,才会执行触发器的主体代码。

2、DML触发器的激活顺序

1)执行before语句级触发器—如果存在这种触发器

2)对受该语句影响的每一行记录

执行before行级触发器—如果存在这种触发器

执行该语句本身

执行after行级触发器--如果存在这种触发器

3)执行after语句级触发器--如果存在这种触发器

同一种类型的触发器的点火次序没有经过定义。如果该次序很重要的话,那么建议将所有这些操作组合到一个触发器当中。

3、行级触发器中的关联标识符

触发器的激活语句每处理一行数据,行级触发器就会激活一次。可以在这种行级触发器内部,访问正被处理的记录行中的数据。这是通过两个关联标识符--:old和:new—实现的。关联标识符也是PL/SQL的一种特殊的绑定变量。标识符前面的冒号,既说明这二者都是绑定变量,同时也说明它们不是一般的PL/SQL变量。PL/SQL编译器会将它们看作下面这个类型的记录:

Triggering_table%ROWTYPE

其中triggering_table是在其上定义触发器的表名。于是,下面这种引用

:new.field

就只有当其中的field是该触发表中的字段名时才会有效。

触发语句

:old

:new

INSERT

未定义—所有字段均为NULL

触发语句完成的时候,要插入的值

UPDATE

更新以前相应记录行的原始值

触发语句完成的时候,要更新的值

DELETE

删除以前相应记录行的原始值

未定义—所有字段均为NULL

注意:INSERT语句上没有定义:old标识符,,DELETE语句上也没有定义:new标识符。如果再INSERT语句上使用:old标识符,或者在DELETE语句上使用:new标识符,PL/SQL并不会产生错误,但是这两个字段值都会为NULL。

伪记录

虽然在语法构成上,会将:new和:old看作triggering_table%ROWTYPE类型的记录,但是,实际上它们并不是记录。因此,那些能够在记录上正常执行的操作,并不能在:new和:old上执行。例如,不能将它们作为一个整体进行赋值。只能对其中的各个字段分别赋值。

view plaincopy to clipboardprint?

CREATE OR REPLACE TRIGGER TempDelete

BEFORE DELETE ON temp_table

FOR EACH ROW

DECLARE

v_TempRec temp_table%ROWTYPE;

BEGIN

/* This is not a legal assignment, since :old is not truly

a record. */

v_TempRec := :old;

/* We can accomplish the same thing, however, by assigning

the fields individually. */

v_TempRec.char_col := :old.char_col;

v_TempRec.num_col := :old.num_col;

END TempDelete;

/

CREATE OR REPLACE TRIGGER TempDelete

BEFORE DELETE ON temp_table

FOR EACH ROW

DECLARE

v_TempRec temp_table%ROWTYPE;

BEGIN

/* This is not a legal assignment, since :old is not truly

a record. */

v_TempRec := :old;

/* We can accomplish the same thing, however, by assigning

the fields individually. */

v_TempRec.char_col := :old.char_col;

v_TempRec.num_col := :old.num_col;

END TempDelete;

/

REFERENCING子句

还可以使用REFERENCING子句,为:old和:new换一个不同的名称。该子句出现在触发事件以后,WHEN子句以前。其语法如下:

REFERENCING [OLD AS old_name] [NEW AS new_name]

在触发器主体中,可以使用:old_name和:new_name分别代替:old和:new。

注意,在REFERENCING子句中关联标识符都不带冒号。

如下面这个例子所示

view plaincopy to clipboardprint?

CREATE OR REPLACE TRIGGER GenerateAuthorID

BEFORE INSERT OR UPDATE ON authors

REFERENCING new AS new_author

FOR EACH ROW

BEGIN

/* Fill in the ID field of authors with the next value from

author_sequence. Since ID is a column in authors, :new.ID

is a valid reference. */

SELECT author_sequence.NEXTVAL

INTO :new_author.ID

FROM dual;

END GenerateAuthorID;

/

CREATE OR REPLACE TRIGGER GenerateAuthorID

BEFORE INSERT OR UPDATE ON authors

REFERENCING new AS new_author

FOR EACH ROW

BEGIN

/* Fill in the ID field of authors with the next value from

author_sequence. Since ID is a column in authors, :new.ID

is a valid reference. */

SELECT author_sequence.NEXTVAL

INTO :new_author.ID

FROM dual;

END GenerateAuthorID;

/

4、WHEN子句

WHEN子句只能在行级触发器中使用。如果在行级触发器的定义中给出了WHEN子句,触发器主体就只对满足WHEN所定义条件的那些记录行执行。WHEN子句的基本形式如下:

WHEN  trigger_condition

其中,trigger_condition是一个布尔表达式。每处理一行记录,都会重新判断该表达式的值。

也可以在trigger_condition内部使用:new和:old记录,但是与REFERENCING子句一样,在trigger_condition内部使用:new和:old时,不需要冒号。仅在触发器主体中才需要使用冒号。

5、触发器谓词

可以在触发器内部使用3个布尔函数,判断触发该触发器的到底是什么操作。这3个谓词分别是INSERTING、UPDATING和DELETING.

使用方法如下面这个例子所示

view plaincopy to clipboardprint?

CREATE OR REPLACE TRIGGER LogInventoryChanges

BEFORE INSERT OR DELETE OR UPDATE ON inventory

FOR EACH ROW

DECLARE

v_ChangeType CHAR(1);

BEGIN

/* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */

IF INSERTING THEN

v_ChangeType := 'I';

ELSIF UPDATING THEN

v_ChangeType := 'U';

ELSE

v_ChangeType := 'D';

END IF;

/* Record all the changes made to inventory in

inventory_audit. Use SYSDATE to generate the timestamp, and

USER to return the userid of the current user. */

INSERT INTO inventory_audit

(change_type, changed_by, timestamp,

old_isbn, old_status, old_status_date, old_amount,

new_isbn, new_status, new_status_date, new_amount)

VALUES

(v_ChangeType, USER, SYSDATE,

:old.isbn, :old.status, :old.status_date, :old.amount,

:new.isbn, :new.status, :new.status_date, :new.amount);

END LogInventoryChanges;

/

CREATE OR REPLACE TRIGGER LogInventoryChanges

BEFORE INSERT OR DELETE OR UPDATE ON inventory

FOR EACH ROW

DECLARE

v_ChangeType CHAR(1);

BEGIN

/* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */

IF INSERTING THEN

v_ChangeType := 'I';

ELSIF UPDATING THEN

v_ChangeType := 'U';

ELSE

v_ChangeType := 'D';

END IF;

/* Record all the changes made to inventory in

inventory_audit. Use SYSDATE to generate the timestamp, and

USER to return the userid of the current user. */

INSERT INTO inventory_audit

(change_type, changed_by, timestamp,

old_isbn, old_status, old_status_date, old_amount,

new_isbn, new_status, new_status_date, new_amount)

VALUES

(v_ChangeType, USER, SYSDATE,

:old.isbn, :old.status, :old.status_date, :old.amount,

:new.isbn, :new.status, :new.status_date, :new.amount);

END LogInventoryChanges;

/

6、INSTEAD-OF触发器

INSTEAD-OF触发器仅可以定义在视图上(关系型的或对象),并且它们可以替代点火它们的DML语句进行点火。INSTEAD-OF触发器必须是行级的。

7、触发器的限制

触发器的主体是一个PL/SQL块。在PL/SQL块中可以使用的所有语句在触发器主体中都是合法的,但是要受到下面限制的约束:

触发器不应该使用事务控制语句—COMMIT、ROLLBACK或SAVEPOINT。触发器作为触发语句执行的一部分被点火,它和触发语句在同一个事务中。当触发语句被提交或撤回提交时,触发器的工作也相应被提交会撤回提交。

由触发器主体调用的任何过程和函数都不能使用事务控制语句。

触发器主体不能声明任何LONG或者LONG RAW变量。而且,:new和:old不能指向定义触发器的表中的LONG和LONG RAW列。

触发器主体可以访问的表有所限制。

触发器P-Code

当包或者子程序存储在数据字典中时,存储的除了该对象的源代码还有经过编译的p-code。但是对于触发器来说就不是这样的。在数据字典中唯一存储的是触发器的源代码,而不是p-code。结果,每次当从数据字典中重新读出触发器时,必须要进行编译。这对触发器的定义和使用的方式不会带来什么影响,但是会影响触发器的性能。

8、系统触发器

我们前面所看到的DML触发器和INSTEAD-OF触发器都是基于DML事件。而另一方面,系统触发器的激活则是基于两种不同的事件:DDL事件或数据库事件。DDL事件包括CREATE、ALTER或DROP语句,而数据库事件包括数据库服务器的启动/关闭事件,用户的登陆/断开事件,以及服务器错误。创建系统触发器的语法如下:

view plaincopy to clipboardprint?

CREATE [OR REPLACE] TRIGGER [schema.]trigger_name

{BEFORE | AFTER}

{ddl_event_list | database_event_list}

ON {DATABASE | [schema.]SCHEMA}

[when_clause]

Trigger_body;

CREATE [OR REPLACE] TRIGGER [schema.]trigger_name

{BEFORE | AFTER}

{ddl_event_list | database_event_list}

ON {DATABASE | [schema.]SCHEMA}

[when_clause]

Trigger_body;

其中,ddl_event_list是由OR关键字隔开的一个或个DDL事件,database_event_list则是由OR关键字隔开的一个或多个数据库事件。

注意:不能创建INSTEAD-OF系统级触发器。

通过子句ON {DATABASE | [schema.]SCHEMA}我们可以指定这个系统触发器是定义在数据库级上还是模式级上。只要发生了激活事件,数据库级触发器就会激活。而只有激活事件发生在某个具体模式中,相应的模式级触发器才会激活。如果使用SCHEMA关键字的时候没有定义某个具体模式的名称,那么默认设置为拥有这个触发器的模式。

9、修改触发器状态和删除触发器

启动或禁用触发器

ALTER TRIGGER trigger_name {DISABLE | ENABLE};

删除触发器

DROP TRIGGER trigger_name;

还可以使用ALTER TABLE命令,并附加使用ENABLE ALL TRIGGERS或DISABLE ALL TRIGGERS子句,同时将某一个表上的所有触发器开启或关闭。

ALTER TABLE table_name {ENABLE | DISABLE} ALL TRIGGERS;

可以通过user_triggers来查看相应触发器信息。

10、变化表和限制表

触发器主体(trigger body)可以访问的表和列上有一些限制。在定义这些限制以前,我们先看两个概念—变化表和限制表。

“变化表”(mutating table)是被DML语句正在修改的表。对于触发器而言,它就是定义触发器的表。需要作为DELETE CASCADE参考完整性限制(referential integrity constraints)的结果进行更新的表也是变化的(mutating)。

“限制表”(constraining table)是可能需要对参考完整性限制执行读操作的表。

为了更好的理解定义,我们看下面这个例子

view plaincopy to clipboardprint?

CREATE TABLE registered_students (

student_id NUMBER(5) NOT NULL,

department CHAR(3)   NOT NULL,

course     NUMBER(3) NOT NULL,

grade      CHAR(1),

CONSTRAINT rs_grade

CHECK (grade IN ('A', 'B', 'C', 'D', 'E')),

CONSTRAINT rs_student_id

FOREIGN KEY (student_id) REFERENCES students (id),

CONSTRAINT rs_department_course

FOREIGN KEY (department, course)

REFERENCES classes (department, course)

);

--...

CREATE TABLE registered_students (

student_id NUMBER(5) NOT NULL,

department CHAR(3)   NOT NULL,

course     NUMBER(3) NOT NULL,

grade      CHAR(1),

CONSTRAINT rs_grade

CHECK (grade IN ('A', 'B', 'C', 'D', 'E')),

CONSTRAINT rs_student_id

FOREIGN KEY (student_id) REFERENCES students (id),

CONSTRAINT rs_department_course

FOREIGN KEY (department, course)

REFERENCES classes (department, course)

);

--...

registered_students有两个声明的参考完整性限制。

Students和classes都是registered_students的限制表。

触发器主体中的SQL不允许进行:

读取或修改触发语句(triggering statement)的任何变化表。这些表包括触发表(triggering table )自己。

读取或修改触发表(triggering table)的限制表中的主键(primary)、唯一列值(unique)或外键(foreign key)列。但是如果需要的话,可以修改其他列。

linux

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
oracle怎么查询所有索引oracle怎么查询所有索引May 13, 2022 pm 05:23 PM

方法:1、利用“select*from user_indexes where table_name=表名”语句查询表中索引;2、利用“select*from all_indexes where table_name=表名”语句查询所有索引。

什么是oracle asm什么是oracle asmApr 18, 2022 pm 04:16 PM

oracle asm指的是“自动存储管理”,是一种卷管理器,可自动管理磁盘组并提供有效的数据冗余功能;它是做为单独的Oracle实例实施和部署。asm的优势:1、配置简单、可最大化推动数据库合并的存储资源利用;2、支持BIGFILE文件等。

oracle全角怎么转半角oracle全角怎么转半角May 13, 2022 pm 03:21 PM

在oracle中,可以利用“TO_SINGLE_BYTE(String)”将全角转换为半角;“TO_SINGLE_BYTE”函数可以将参数中所有多字节字符都替换为等价的单字节字符,只有当数据库字符集同时包含多字节和单字节字符的时候有效。

Oracle怎么查询端口号Oracle怎么查询端口号May 13, 2022 am 10:10 AM

在Oracle中,可利用lsnrctl命令查询端口号,该命令是Oracle的监听命令;在启动、关闭或重启oracle监听器之前可使用该命令检查oracle监听器的状态,语法为“lsnrctl status”,结果PORT后的内容就是端口号。

oracle怎么删除sequenceoracle怎么删除sequenceMay 13, 2022 pm 03:35 PM

在oracle中,可以利用“drop sequence sequence名”来删除sequence;sequence是自动增加数字序列的意思,也就是序列号,序列号自动增加不能重置,因此需要利用drop sequence语句来删除序列。

oracle怎么查询数据类型oracle怎么查询数据类型May 13, 2022 pm 04:19 PM

在oracle中,可以利用“select ... From all_tab_columns where table_name=upper('表名') AND owner=upper('数据库登录用户名');”语句查询数据库表的数据类型。

oracle查询怎么不区分大小写oracle查询怎么不区分大小写May 10, 2022 pm 05:45 PM

方法:1、利用“LOWER(字段值)”将字段转为小写,或者利用“UPPER(字段值)”将字段转为大写;2、利用“REGEXP_LIKE(字符串,正则表达式,'i')”,当参数设置为“i”时,说明进行匹配不区分大小写。

Oracle怎么修改sessionOracle怎么修改sessionMay 13, 2022 pm 05:06 PM

方法:1、利用“alter system set sessions=修改后的数值 scope=spfile”语句修改session参数;2、修改参数之后利用“shutdown immediate – startup”语句重启服务器即可生效。

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

Hot Tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!