Oracle数据库中的触发器

Rivarola 2015-03-11

触发器是一种特殊的存储过程,它在发生某种数据库事件时由Oracle系统自动触发。触发器通常用于加强数据库的完整性约束和业务规则等,对于表来说,触发器可以实现比CHECK约束更为复杂的约束。
Oracle中的触发器的类型主要有DML触发器、替代触发器、系统事件触发器和DDL触发器。

触发器的分类
DML触发器
DML触发器由DML语句触发,例如INSERT、UPDATE和DELETE语句,其对应的trigger_event具体内容如下:
{INSERT|DELETE|UPDATE[OF column[,...]]}1
针对所有的DML事件,按照触发器的触发时间可以将DML触发器分为BEFORE触发器和AFTER触发器,分别表示在DML事件发生之前与之后采取行动。

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

BEFORE行触发器
在开发数据库应用时,为了确保数据符合商业或企业规则,应该使用约束对输入数据加以限制,在某些情况下约束可能无法是想复杂的商业逻辑或企业规则,此时因考虑BEFORE行触发器。
AFTER行触发器
为了审计DML操作,可以使用语句级触发器或Oracle提供的审计功能,而为了审计数据变化,则应使用AFTER行触发器。

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

另外,DML触发器也可以分为语句级触发器和行级触发器,语句级触发器针对某一条语句触发一次,而行级触发器针对语句所影响的每一行都触发一次。
可以将DML操作细化到列,即针对某列进行DML操作时激活触发器。
在进行行级触发器中,为了获取某列在DML操作前后的数据,Oracle提供了两种特殊的标示符::OLD和:NEW,通过:OLD.column_name和:NEW.column_name分别获取该列的旧数据和新数据。INSERT触发器只能使用:NEW,DELETE只能使用:OLD,而UPDATE则两种都可以使用。

例1:
1.首先以SYSDBA身份登录,创建用户SIEGE,赋予相关权限(此步可省略):

CREATE USER siege IDENTIFIED BY "123456";
GRANT CREATE SESSION TO siege;
GRANT CREATE TABLE TO siege;
GRANT CREATE TABLESPACE TO siege;
GRANT CREATE VIEW TO siege;
GRANT CREATE ANY TRIGGER TO SIEGE;
CREATE TABLESPACE learning;
DATAFILE 'D:\oraclexe\app\oracle\oradata\XE\leaning.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

ALTER USER SIEGE DEFAULT TABLESPACE leaning;
ALTER USER SIEGE QUOTA UNLIMITED ON leaning;  1
2.创建student表和record表,并插入相关数据:

CREATE TABLE student(
    sid  NUMBER(4),
    sname VARCHAR2(10),
    sage NUMBER(4)
);
CREATE TABLE record(
    content VARCHAR2(80),
    rtime timestamp
);
INSERT INTO student VALUES(1001,'CANDY',24);
INSERT INTO student VALUES(1002,'TRACY',25);
INSERT INTO student VALUES(1003,'SAM',23);1
3.创建student表的触发器:

CREATE OR REPLACE TRIGGER update_student_trigger
AFTER UPDATE
ON student
FOR EACH ROW
BEGIN
  INSERT INTO RECORD VALUES('执行了UPDATE操作,执行该操作前的数据为:sid='||:OLD.sid,SYSDATE);
END    update_student_trigger;1
4.测试触发器:
修改student表数据:

UPDATE student SET  sage=26;1
此时查看record数据,发现更新了3条数据,说明该触发器为行触发器:

1 执行了UPDATE操作,执行该操作前的数据为:sid=1001 15-FEB-15 00.03.42.000000
2 执行了UPDATE操作,执行该操作前的数据为:sid=1002 15-FEB-15 00.03.42.000000
3 执行了UPDATE操作,执行该操作前的数据为:sid=1003 15-FEB-15 00.03.42.000000

INSTEAD OF触发器
INSTEAD OF触发器(替代触发器),用于执行一个替代操作来代替触发事件的操作,例如针对INSERT事件的INSTEAD OF触发器,它由INSERT语句触发,当出现INSERT语句时,该语句不会被执行,而是执行INSTEAD OF触发器中定义的语句。
不过,Oracle中的INSTEAD OF触发器不能针对表,而只能针对视图。并不是视图的所有列都支持, 例如对列进行了数学或函数计算,则不能对该列进行DML操作,这时可以使用INSTEAD OF触发器。
例2:
针对例1中的student表进行创建视图,其语句如下:

CREATE VIEW student_view
AS
SELECT sid,sname,sage+1 NEW_age
FROM student
WITH CHECK OPTION;1
创建好视图后,进行插入操作:

INSERT INTO student_view VALUES(1004,'PETER',22);1
此时,报ORA-01773错误。对此我们应该对student_view创建INSTEAD OF触发器:

CREATE OR REPLACE TRIGGER insteadof_student_view
INSTEAD OF INSERT
ON student_view
FOR EACH ROW
BEGIN
INSERT INTO student (sid,sname,sage)
VALUES(:NEW.sid,:NEW.sname,:NEW.new_age);
END insteadof_student_view;
1
创建好触发器后,再次执行上面的插入语句,则可以成功进行插入了。

系统事件触发器
系统事件触发器在发生如数据库启动或关闭等系统事件时触发。创建系统事件触发器需要使用ON DATABASE子句,即表示创建的触发器是数据库级触发器,同时创建系统事件触发器需要具有DBA权限。系统事件如下:

系统事件说明
LOGOFF用户从数据库注销
LOGON用户登录数据库
SERVERERROR服务器发生错误
SHUTDOWN关闭数据库实例

例3:
以SYSDBA身份登录,创建table_event:

CREATE TABLE  table_event(
      event VARCHAR2(30),
      time DATE
)
1
然后创建tr_startup触发器和tr_shutdown触发器:

CREATE OR REPLACE TRIGGER tr_startup
AFTER startup ON DATABASE
BEGIN
      INSERT INTO table_event VALUES (ora_sysevent,SYSDATE);
END tr_startup;   
1

--------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER tr_shutdown
BEFORE shutdown ON DATABASE
BEGIN
      INSERT INTO table_event  VALUES (ora_sysevent,SYSDATE);
END tr_shutdown;    1
然后关闭和启动数据库,查看table_event表:

SHUTDOWN 16/02/2015 01:01:02
STARTUP 16/02/2015 01:01:56

说明系统触发器已起作用了。

DDL触发器
DDL触发器由DDL语句触发,如CREATE、ALTER和DROP语句。DDL触发器同样分为BEFORE触发器与AFTER触发器。
触发器的创建
创建触发器需要使用CREATE TRIGGER语句,其语法如下:

CREATE [OR REPLACE] TRIGGER trigger_name
[BEFORE|AFTER|INSTEAD OF] trigger_event
{ON table_name|view_name|DATABASE}
[FOR EACH ROW]
[ENABLE|DISABLE]
[WHEN trigger_condition]
[DECLARE declaration_statements;]
BEGIN
    trigger_body;
END [trigger_name];

相关推荐