oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发.docx

oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发.docx

《oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发.docx》由会员分享，可在线阅读，更多相关《oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发.docx（39页珍藏版）》请在冰豆网上搜索。

oracle触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发

oracle触发器的种类和触发事件,DML触发器,DDL事件触发器,替代触发器,查看触发...

oracle数据库开发应用实例,招生录取系统...|oracle创建,删除存储过程,参数传递,创...

2009-04-14

oracle触发器的种类和触发事件,DML触发器,DDL事件触发器,替代触发器,查看触发器,

关键字:

oracle触发器种类触发事件dmlddl事件替代查看

触发器的种类和触发事件

触发器必须由事件才能触发。

触发器的触发事件分可为3类，分别是DML事件、DDL事件和数据库事件。

每类事件包含若干个事件，如下所示。

数据库的事件是具体的，在创建触发器时要指明触发的事件。

种类关键字含义Sql代码

DML事件（3种）INSERT在表或视图中插入数据时触发

UPDATE修改表或视图中的数据时触发

DELETE在删除表或视图中的数据时触发

DDL事件（3种）CREATE在创建新对象时触发

ALTER修改数据库或数据库对象时触发

DROP删除对象时触发

数据库事件（5种）STARTUP数据打开时触发

SHUTDOWN在使用NORMAL或IMMEDIATE选项关闭数据库时触发

LOGON当用户连接到数据库并建立会话时触发

LOGOFF当一个会话从数据库中断开时触发

SERVERERROR发生服务器错误时触发

DML事件（3种）INSERT在表或视图中插入数据时触发

UPDATE修改表或视图中的数据时触发

DELETE在删除表或视图中的数据时触发

DDL事件（3种）CREATE在创建新对象时触发

ALTER修改数据库或数据库对象时触发

DROP删除对象时触发

数据库事件（5种）STARTUP数据打开时触发

SHUTDOWN在使用NORMAL或IMMEDIATE选项关闭数据库时触发

LOGON当用户连接到数据库并建立会话时触发

LOGOFF当一个会话从数据库中断开时触发

SERVERERROR发生服务器错误时触发

触发器的类型可划分为4种：

数据操纵语言（DML）触发器、替代（INSTEADOF）触发器、数据定义语言（DDL）触发器和数据库事件触发器。

各类触发器的作用如下所示。

Sql代码

种类简称作用

数据操纵语言触发器DML触发器创建在表上，由DML事件引发的触发器

替代触发器INSTEADOF触发器创建在视图上，用来替换对视图进行的插入、删除和修改操作

数据定义语言触发器DDL触发器定义在模式上，触发事件是数据库对象的创建和修改

数据库事件触发器—定义在整个数据库或模式上，触发事件是数据库事件

种类简称作用

数据操纵语言触发器DML触发器创建在表上，由DML事件引发的触发器

替代触发器INSTEADOF触发器创建在视图上，用来替换对视图进行的插入、删除和修改操作

数据定义语言触发器DDL触发器定义在模式上，触发事件是数据库对象的创建和修改

数据库事件触发器—定义在整个数据库或模式上，触发事件是数据库事件

DML触发器的要点

DML触发器是定义在表上的触发器，由DML事件引发。

编写DML触发器的要素是：

*确定触发的表，即在其上定义触发器的表。

*确定触发的事件，DML触发器的触发事件有INSERT、UPDATE和DELETE三种，说明见下。

*确定触发时间。

触发的时间有BEFORE和AFTER两种，分别表示触发动作发生在DML语句执行之前和语句执行之后。

*确定触发级别，有语句级触发器和行级触发器两种。

语句级触发器表示SQL语句只触发一次触发器，行级触发器表示SQL语句影响的每一行都要触发一次。

由于在同一个表上可以定义多个DML触发器，因此触发器本身和引发触发器的SQL语句在执行的顺序上有先后的关系。

它们的顺序是：

*如果存在语句级BEFORE触发器，则先执行一次语句级BEFORE触发器。

*在SQL语句的执行过程中，如果存在行级BEFORE触发器，则SQL语句在对每一行操作之前，都要先执行一次行级BEFORE触发器，然后才对行进行操作。

如果存在行级AFTER触发器，则SQL语句在对每一行操作之后，都要再执行一次行级AFTER触发器。

*如果存在语句级AFTER触发器，则在SQL语句执行完毕后，要最后执行一次语句级AFTER触发器。

DML触发器还有一些具体的问题，说明如下：

*如果有多个触发器被定义成为相同时间、相同事件触发，且最后定义的触发器是有效的，则最后定义的触发器被触发，其他触发器不执行。

*一个触发器可由多个不同的DML操作触发。

在触发器中，可用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。

这些谓词可以在IF分支条件语句中作为判断条件来使用。

*在行级触发器中，用:

new和:

old（称为伪记录）来访问数据变更前后的值。

但要注意，INSERT语句插入一条新记录，所以没有:

old记录，而DELETE语句删除掉一条已经存在的记录，所以没有:

new记录。

UPDATE语句既有:

old记录，也有:

new记录，分别代表修改前后的记录。

引用具体的某一列的值的方法是：

ld.字段名或:

new.字段名

*触发器体内禁止使用COMMIT、ROLLBACK、SAVEPOINT语句，也禁止直接或间接地调用含有上述语句的存储过程。

定义一个触发器时要考虑上述多种情况，并根据具体的需要来决定触发器的种类。

DML触发器的创建

创建DML触发器需要CREATETRIGGER系统权限。

创建DML触发器的语法如下：

CREATE[ORREPLACE]TRIGGER触发器名

{BEFORE|AFTER|INSTEADOF}触发事件1[OR触发事件2...]

ON表名

WHEN触发条件

[FOREACHROW]

DECLARE

声明部分

BEGIN

主体部分

END;

其中：

ORREPLACE：

表示如果存在同名触发器，则覆盖原有同名触发器。

BEFORE、AFTER和INSTEADOF：

说明触发器的类型。

WHEN触发条件：

表示当该条件满足时，触发器才能执行。

触发事件：

指INSERT、DELETE或UPDATE事件，事件可以并行出现，中间用OR连接。

对于UPDATE事件，还可以用以下形式表示对某些列的修改会引起触发器的动作：

UPDATEOF列名1，列名2...

ON表名：

表示为哪一个表创建触发器。

FOREACHROW：

表示触发器为行级触发器，省略则为语句级触发器。

触发器的创建者或具有DROPANYTIRGGER系统权限的人才能删除触发器。

删除触发器的语法如下：

DROPTIRGGER触发器名

可以通过命令设置触发器的可用状态，使其暂时关闭或重新打开，即当触发器暂时不用时，可以将其置成无效状态，在使用时重新打开。

该命令语法如下：

ALTERTRIGGER触发器名{DISABLE|ENABLE}

其中，DISABLE表示使触发器失效，ENABLE表示使触发器生效。

同存储过程类似，触发器可以用SHOWERRORS检查编译错误。

行级触发器的应用

在行级触发器中，SQL语句影响的每一行都会触发一次触发器，所以行级触发器往往用在对表的每一行的操作进行控制的场合。

若在触发器定义中出现FOREACHROW子句，则为语句级触发器。

【训练1】创建包含插入、删除、修改多种触发事件的触发器DML_LOG，对EMP表的操作进行记录。

用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。

在创建触发器之前，需要先创建事件记录表LOGS，该表用来对操作进行记录。

该表的字段含义解释如下：

LOG_ID：

操作记录的编号，数值型，它是该表的主键，由序列自动生成。

LOG_TABLE：

进行操作的表名，字符型，非空，该表设计成可以由多个触发器共享使用。

比如我们可以为dept表创建类似的触发器，同样将操作记录到该表。

LOG_DML：

操作的动作，即INSERT、DELETE或UPDATE三种之一。

LOG_KEY_ID：

操作时表的主键值，数值型。

之所以记录表的主键，是因为主键是表的记录的惟一标识，可以识别是对哪一条记录进行了操作。

对于emp表，主键是empno。

LOG_DATE：

操作的日期，日期型，取当前的系统时间。

LOG_USER：

操作者，字符型，取当时的操作者账户名。

比如登录SCOTT账户进行操作，在该字段中，记录账户名为SCOTT。

步骤1：

在SQL*Plus中登录STUDENT账户，创建如下的记录表LOGS：

Sql代码

CREATETABLElogs（

LOG_IDNUMBER（10）PRIMARYKEY,

LOG_TABLEVARCHAR2（10）NOTNULL,

LOG_DMLVARCHAR2（10）,

LOG_KEY_IDNUMBER（10）,

LOG_DATEDATE,

LOG_USERVARCHAR2（15）

）;

CREATETABLElogs（

LOG_IDNUMBER（10）PRIMARYKEY,

LOG_TABLEVARCHAR2（10）NOTNULL,

LOG_DMLVARCHAR2（10）,

LOG_KEY_IDNUMBER（10）,

LOG_DATEDATE,

LOG_USERVARCHAR2（15）

）;

执行结果：

Sql代码

表已创建。

表已创建。

步骤2：

创建一个LOGS表的主键序列LOGS_ID_SEQ：

Sql代码

CREATESEQUENCElogs_id_squINCREMENTBY1

STARTWITH1MAXVALUE9999999NOCYCLENOCACHE;

CREATESEQUENCElogs_id_squINCREMENTBY1

STARTWITH1MAXVALUE9999999NOCYCLENOCACHE;

执行结果：

Sql代码

序列已创建。

序列已创建。

步骤3：

创建和编译以下触发器：

Sql代码

CREATEORREPLACETRIGGERDML_LOG

BEFORE--触发时间为操作前

DELETEORINSERTORUPDATE--由三种事件触发

ONemp

FOREACHROW--行级触发器

BEGIN

IFINSERTINGTHEN

INSERTINTOlogsVALUES（logs_id_squ.NEXTVAL,'EMP','INSERT',:

new.empno,SYSDATE,USER）;

ELSIFDELETINGTHEN

INSERTINTOlogsVALUES（logs_id_squ.NEXTVAL,'EMP','DELETE',:

old.empno,SYSDATE,USER）;

ELSE

INSERTINTOlogsVALUES（logs_id_squ.NEXTVAL,'EMP','UPDATE',:

new.empno,SYSDATE,USER）;

ENDIF;

END;

CREATEORREPLACETRIGGERDML_LOG

BEFORE--触发时间为操作前

DELETEORINSERTORUPDATE--由三种事件触发

ONemp

FOREACHROW--行级触发器

BEGIN

IFINSERTINGTHEN

INSERTINTOlogsVALUES（logs_id_squ.NEXTVAL,'EMP','INSERT',:

new.empno,SYSDATE,USER）;

ELSIFDELETINGTHEN

INSERTINTOlogsVALUES（logs_id_squ.NEXTVAL,'EMP','DELETE',:

old.empno,SYSDATE,USER）;

ELSE

INSERTINTOlogsVALUES（logs_id_squ.NEXTVAL,'EMP','UPDATE',:

new.empno,SYSDATE,USER）;

ENDIF;

END;

执行结果：

触发器已创建

步骤4：

在EMP表中插入记录：

Sql代码

INSERTINTOemp（empno,ename,job,sal）VALUES（8001,'MARY','CLERK',1000）;

COMMIT;

INSERTINTOemp（empno,ename,job,sal）VALUES（8001,'MARY','CLERK',1000）;

COMMIT;

执行结果：

Sql代码

已创建1行。

提交完成。

已创建1行。

提交完成。

步骤5：

检查LOGS表中记录的信息：

Sql代码

SELECT*FROMLOGS;

SELECT*FROMLOGS;

执行结果为：

Sql代码

LOG_IDLOG_TABLELOG_DMLLOG_KEY_IDLOG_DATELOG_USER

--------------------------------------------------------------------------------------------------------------

1EMPINSERT800129-3月-04STUDENT

已选择1行。

LOG_IDLOG_TABLELOG_DMLLOG_KEY_IDLOG_DATELOG_USER

--------------------------------------------------------------------------------------------------------------

1EMPINSERT800129-3月-04STUDENT

已选择1行。

说明：

本例中在emp表上创建了一个由INSERT或DELETE或UPDATE事件触发的行级触发器，触发器的名称是LOG_EMP。

对于不同的操作，记录的内容不同。

本例中只插入了一条记录，如果用一条不带WHERE条件的UPDATE语句来修改所有雇员的工资，则将逐行触发触发器。

INSERT、DELETE和UPDATE都能引发触发器动作，在分支语句中使用INSERTING、DELETING和UPDATING来区别是由哪种操作引发的触发器动作。

在本例的插入动作中，LOG_ID字段由序列LOG_ID_SQU自动填充为1；LOGS表LOG_KEY_ID字段记录的是新插入记录的主键8001；LOD_DML字段记录的是插入动作INSERT；LOG_TABLE字段记录当前表名EMP；LOG_DATE字段记录插入的时间04年3月1日；LOG_USER字段记录插入者STUDENT。

【练习1】修改、删除刚刚插入的雇员记录，提交后检查LOGS表的结果。

【练习2】为DEPT表创建同样的触发器，使用LOGS表进行记录，并检验结果。

【训练2】创建一个行级触发器LOG_SAL，记录对职务为CLERK的雇员工资的修改，且当修改幅度超过200时才进行记录。

用WHEN条件限定触发器。

在创建触发器之前，需要先创建事件记录表LOGERR，该表用来对操作进行记录。

该表的字段含义解释如下：

NUM：

数值型，用于记录序号。

MESSAGE：

字符型，用于记录错误信息。

步骤1：

在SQL*Plus中登录STUDENT账户，创建如下的记录表LOGERR：

Sql代码

CREATETABLElogerr（

NUMNUMBER（10）NOTNULL,

MESSAGEVARCHAR2（50）NOTNULL

）;

CREATETABLElogerr（

NUMNUMBER（10）NOTNULL,

MESSAGEVARCHAR2（50）NOTNULL

）;

执行结果：

Sql代码

表已创建。

表已创建。

步骤2：

创建和编译以下触发器：

Sql代码

CREATEORREPLACETRIGGERlog_sal

BEFORE

UPDATEOFsal

ONemp

FOREACHROW

WHEN（new.job='CLERK'AND（ABS（new.sal-old.sal）>200））

DECLARE

v_noNUMBER;

BEGIN

SELECTCOUNT（*）INTOv_noFROMlogerr;

INSERTINTOlogerrVALUES（v_no+1,'雇员'||:

new.ename||'的原工资：

'||:

old.sal||'新工资：

'||:

new.sal）;

END;

CREATEORREPLACETRIGGERlog_sal

BEFORE

UPDATEOFsal

ONemp

FOREACHROW

WHEN（new.job='CLERK'AND（ABS（new.sal-old.sal）>200））

DECLARE

v_noNUMBER;

BEGIN

SELECTCOUNT（*）INTOv_noFROMlogerr;

INSERTINTOlogerrVALUES（v_no+1,'雇员'||:

new.ename||'的原工资：

'||:

old.sal||'新工资：

'||:

new.sal）;

END;

执行结果：

Sql代码

触发器已创建。

触发器已创建。

步骤3：

在EMP表中更新记录：

Sql代码

UPDATEempSETsal=sal+550WHEREempno=7788;

UPDATEempSETsal=sal+500WHEREempno=7369;

UPDATEempSETsal=sal+50WHEREempno=7876;

COMMIT;

UPDATEempSETsal=sal+550WHEREempno=7788;

UPDATEempSETsal=sal+500WHEREempno=7369;

UPDATEempSETsal=sal+50WHEREempno=7876;

COMMIT;

执行结果：

Sql代码

已更新1行。

已更新1行。

已更新1行。

提交完成。

已更新1行。

已更新1行。

已更新1行。

提交完成。

步骤4：

检查LOGSAL表中记录的信息：

Sql代码

SELECT*FROMlogerr;

SELECT*FROMlogerr;

执行结果为：

Sql代码

NUMMESSAGE

--------------------------------------------------------------------------

1雇员SMITH的原工资：

800新工资：

1300

已选择1行。

NUMMESSAGE

--------------------------------------------------------------------------

1雇员SMITH的原工资：

800新工资：

1300

已选择1行。

说明：

本例中，在emp表的sal列上创建了一个由UPDATE事件触发的行级触发器，触发器的名称是LOG_SAL。

该触发器由WHEN语句限定，只有当被修改工资的雇员职务为CLERK，且修改的工资超过200时才进行触发，否则不进行触发。

所以在验证过程中，虽然修改了3条记录，但通过查询语句发现：

第一条修改语句修改编号为7788的SCOTT记录，因为SCOTT的职务是ANALYST，不符合WHEN条件，没有引起触发器动作；第二条修改语句修改编号为7369的SMITH的记录，职务为CLERK，因为增加的工资（500）超过了200，所以引起触发器动作，并在LOGERR表中进行了记录；第三条修改语句修改编号为7876的雇员ADAMS的记录，虽然ADAMS的职务为CLERK，但修改的工资（50）没有超过200，所以没有引起触发器动作。

注意：

在WHEN条件中引用new和old不需要在前面加“:

”。

在以上实例中，记录了对工资的修改超出范围的信息，但没有限制对工资的修改。

那么当对雇员工资的修改幅度不满足条件时，能否直接限制对工资的修改呢？

答案是肯定的。

【训练3】创建触发器CHECK_SAL，当对职务为CLERK的雇员的工资修改超出500至2000的范围时，进行限制。

步骤1：

创建和编译以下触发器：

Sql代码

CREATEORREPLACETRIGGERCHECK_SAL

BEFORE

UPDATE

ONemp

FOREACHROW

BEGIN

IF:

new.job='CLERK'AND（:

new.sal<500OR:

new.sal>2000）THEN

RAISE_APPLICATION_ERROR（-20001,'工资修改超出范围,操作取消！

'）;

ENDIF;

END;

CREATEORREPLACETRIGGERCHECK_SAL

BEFORE

UPDATE

ONemp

FOREACHROW

BEGIN

IF:

new.job='CLERK'AND（:

new.sal<500OR:

new.sal>2000）THEN

RAISE_APPLICATION_ERROR（-20001,'工资修改超出范围,操作取消！

'）;

ENDIF;

END;

执行结果：

Sql代码

触发器已创建。

触发器已创建。

步骤2：

在EMP表中插入记录：

Sql代码

UPDATEempSETsal=800WHEREempno=7876;

UPDATEempSETsal=450WHEREempno=7876;

COMMIT;

UPDATEempSETsal=800WHEREempno=7876;

UPDATEempSETsal=450