Oracle数据库中的触发器

Oracle数据库中的触发器

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

触发器的分类

• DML触发器
  DML触发器由DML语句触发，例如INSERT、UPDATE和DELETE语句,其对应的trigger_event具体内容如下：

{INSERT|DELETE|UPDATE[OF column[，...]]}

针对所有的DML事件，按照触发器的触发时间可以将DML触发器分为BEFORE触发器和AFTER触发器，分别表示在DML事件发生之前与之后采取行动。

---

1. BEFORE行触发器
   在开发数据库应用时，为了确保数据符合商业或企业规则，应该使用约束对输入数据加以限制，在某些情况下约束可能无法是想复杂的商业逻辑或企业规则，此时因考虑BEFORE行触发器。
2. 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;  

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);

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;

4.测试触发器：
修改student表数据：

UPDATE student SET  sage=26;

此时查看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;

创建好视图后，进行插入操作：

INSERT INTO student_view VALUES(1004,'PETER',22);

此时，报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;

创建好触发器后，再次执行上面的插入语句，则可以成功进行插入了。

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

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

例3：

以SYSDBA身份登录，创建table_event：

CREATE TABLE  table_event(
       event VARCHAR2(30),
       time DATE
)

然后创建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;     

---

CREATE OR REPLACE TRIGGER tr_shutdown
BEFORE shutdown ON DATABASE
BEGIN 
       INSERT INTO table_event  VALUES (ora_sysevent,SYSDATE);
END tr_shutdown;     

然后关闭和启动数据库，查看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];