oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发Word下载.docx
- 文档编号:18345521
- 上传时间:2022-12-15
- 格式:DOCX
- 页数:39
- 大小:27.91KB
oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发Word下载.docx
《oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发Word下载.docx》由会员分享,可在线阅读,更多相关《oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发Word下载.docx(39页珍藏版)》请在冰豆网上搜索。
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:
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)
);
执行结果:
表已创建。
步骤2:
创建一个LOGS表的主键序列LOGS_ID_SEQ:
CREATESEQUENCElogs_id_squINCREMENTBY1
STARTWITH1MAXVALUE9999999NOCYCLENOCACHE;
CREATESEQUENCElogs_id_squINCREMENTBY1
STARTWITH1MAXVALUE9999999NOCYCLENOCACHE;
序列已创建。
步骤3:
创建和编译以下触发器:
CREATEORREPLACETRIGGERDML_LOG
BEFORE--触发时间为操作前
DELETEORINSERTORUPDATE--由三种事件触发
ONemp
FOREACHROW--行级触发器
BEGIN
IFINSERTINGTHEN
INSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'
EMP'
'
INSERT'
:
new.empno,SYSDATE,USER);
ELSIFDELETINGTHEN
DELETE'
old.empno,SYSDATE,USER);
ELSE
UPDATE'
ENDIF;
END;
CREATEORREPLACETRIGGERDML_LOG
BEFORE--触发时间为操作前
DELETEORINSERTORUPDATE--由三种事件触发
ONemp
FOREACHROW--行级触发器
BEGIN
IFINSERTINGTHEN
INSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'
ELSIFDELETINGTHEN
ELSE
ENDIF;
触发器已创建
步骤4:
在EMP表中插入记录:
INSERTINTOemp(empno,ename,job,sal)VALUES(8001,'
MARY'
CLERK'
1000);
COMMIT;
COMMIT;
已创建1行。
提交完成。
提交完成。
步骤5:
检查LOGS表中记录的信息:
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:
字符型,用于记录错误信息。
在SQL*Plus中登录STUDENT账户,创建如下的记录表LOGERR:
CREATETABLElogerr(
NUMNUMBER(10)NOTNULL,
MESSAGEVARCHAR2(50)NOTNULL
CREATETABLElogerr(
NUMNUMBER(10)NOTNULL,
MESSAGEVARCHAR2(50)NOTNULL
CREATEORREPLACETRIGGERlog_sal
BEFORE
UPDATEOFsal
FOREACHROW
WHEN(new.job='
AND(ABS(new.sal-old.sal)&
gt;
200))
v_noNUMBER;
SELECTCOUNT(*)INTOv_noFROMlogerr;
INSERTINTOlogerrVALUES(v_no+1,'
雇员'
||:
new.ename||'
的原工资:
'
old.sal||'
新工资:
new.sal);
CREATEORREPLACETRIGGERlog_sal
BEFORE
UPDATEOFsal
FOREACHROW
WHEN(new.job='
200))
DECLARE
v_noNUMBER;
SELECTCOUNT(*)INTOv_noFROMlogerr;
INSERTINTOlogerrVALUES(v_no+1,'
触发器已创建。
在EMP表中更新记录:
UPDATEempSETsal=sal+550WHEREempno=7788;
UPDATEempSETsal=sal+500WHEREempno=7369;
UPDATEempSETsal=sal+50WHEREempno=7876;
UPDATEempSETsal=sal+500WHEREempno=7369;
UPDATEempSETsal=sal+50WHEREempno=7876;
已更新1行。
已更新1行。
检查LOGSAL表中记录的信息:
SELECT*FROMlogerr;
NUMMESSAGE
--------------------------------------------------------------------------
1雇员SMITH的原工资:
800新工资:
1300
NUMMESSAGE
--------------------------------------------------------------------------
1雇员SMITH的原工资:
1300
说明:
本例中,在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不需要在前面加&
#8220;
:
&
#8221;
。
在以上实例中,记录了对工资的修改超出范围的信息,但没有限制对工资的修改。
那么当对雇员工资的修改幅度不满足条件时,能否直接限制对工资的修改呢?
答案是肯定的。
【训练3】创建触发器CHECK_SAL,当对职务为CLERK的雇员的工资修改超出500至2000的范围时,进行限制。
CREATEORREPLACETRIGGERCHECK_SAL
UPDATE
IF:
new.job='
AND(:
new.sal&
lt;
500OR:
2000)THEN
RAISE_APPLICATION_ERROR(-20001,'
工资修改超出范围,操作取消!
CREATEORREPLACETRIGGERCHECK_SAL
UPDATE
2000)THEN
RAISE_APPLICATION_ERROR(-20001,'
UPDATEempSETsal=800WHEREempno=7876;
UPDATEempSETsal=450WHEREempno=7876;
UPDATEempSETsal=450
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发 触发器 种类 触发 事件 dml ddl 替代 查看
![提示](https://static.bdocx.com/images/bang_tan.gif)
链接地址:https://www.bdocx.com/doc/18345521.html