Oracle触发器1-介绍

Oracle官方参考：PL/SQL Language Referenc->9 PL/SQL Trigger

Reasons to Use Trigger:

■ Automatically generate calculated column values 
■ Log events
■ Gather statistics on table access
■ Modify table data when DML statements are issued against views
■ Enforce referential integrity when child and parent tables are on different nodes of a distributed database
■ Publish information about database events, user events, and SQL statements to subscribing applications
■ Prevent DML operations on a table after regular business hours
■ Prevent invalid transactions
■ Enforce complex business or referential integrity rules that you cannot define with constraints

本人E比较差，防止误导别人，也就不一一解释。

触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能像存储过程一样接收参数。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

DML触发器

ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。

DDL触发器

创建DDL触发器语法和DML触发器很类似，只是触发事件不同（create table,alter index,drop trigger等），这种触发器不是作用于某个表的。

替代触发器

由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。

数据库事件触发器

ORACLE 提供了第三种类型的触发器，数据库事件触发器也叫系统触发器。它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。

      触发器组成部分

1、 触发器名称
2、 触发语句
3、 触发器限制
4、 触发操作

Trigger Design Guidelines:

■ Use triggers to ensure that whenever a specific event occurs, any necessary actions are done (regardless of which user or application issues the triggering statement).
For example, use a trigger to ensure that whenever anyone updates a table, its log file is updated.
■ Do not create triggers that duplicate database features.
For example, do not create a trigger to reject invalid data if you can do the same with constraints.
■ Do not create triggers that depend on the order in which a SQL statement processes rows (which can vary).
For example, do not assign a value to a global package variable in a row trigger if the current value of the variable depends on the row being processed by the row trigger. If a trigger updates global package variables, initialize those variables in a BEFORE statement trigger.
■ Use BEFORE row triggers to modify the row before writing the row data to disk.
■ Use AFTER row triggers to obtain the row ID and use it in operations.An AFTER row trigger fires when the triggering statement results in ORA-2292.

■ If the triggering statement of a BEFORE statement trigger is an UPDATE or DELETEstatement that conflicts with an UPDATE statement that is running, then the
database does a transparent ROLLBACK to SAVEPOINT and restarts the triggering statement. The database can do this many times before the triggering statement
completes successfully. Each time the database restarts the triggering statement, the trigger fires. The ROLLBACK to SAVEPOINT does not undo changes to package
variables that the trigger references. To detect this situation, include a counter variable in the package.
■ Do not create recursive triggers.
For example, do not create an AFTER UPDATE trigger that issues an UPDATE statement on the table on which the trigger is defined.

The trigger fires recursively until it runs out of memory

■ Use DATABASE triggers judiciously. They fire every time any database user initiates a triggering event.
■ If a trigger runs the following statement, the statement returns the owner of the trigger, not the user who is updating the table:
SELECT Username FROM USER_USERS;
■ Only committed triggers fire.
A trigger is committed, implicitly, after the CREATE TRIGGER statement that creates it succeeds.

Therefore, the following statement cannot fire the trigger that it creates:
CREATE OR REPLACE TRIGGER my_trigger
  AFTER CREATE ON DATABASE
BEGIN
  NULL;
END;
/
■ To allow the modular installation of applications that have triggers on the same tables, create multiple triggers of the same type, rather than a single trigger that
runs a sequence of operations.Each trigger sees the changes made by the previously fired triggers. Each trigger can see OLD and NEW values.