oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发.docx
《oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发.docx》由会员分享,可在线阅读,更多相关《oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发.docx(39页珍藏版)》请在冰豆网上搜索。
![oracle 触发器的种类和触发事件dml触发器ddl事件触发器替代触发器查看触发.docx](https://file1.bdocx.com/fileroot1/2022-12/15/3c99e111-3ffc-4e58-93f6-1db50fa5a5cf/3c99e111-3ffc-4e58-93f6-1db50fa5a5cf1.gif)
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