Oracle数据库中的触发器

Oracle数据库中的触发器

触发器是一种特殊的存储过程，它在发生某种数据库事件时由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权限。系统事件如下：

例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];