PL/SQL之触发器创建

一，触发器的创建语法解析

触发器创建语法：

--触发器创建语法
CREATE [OR REPLACE] TRIGGER trigger_name--触发器名字
 {BEFORE|AFTER}--触发时间
 {INSERT|UPDATE|DELETE[OF COLUMN]}
 ON [schema.]table_name | [shcema.]view_name--触发对象
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]--触发频率
[WHEN condition]--触发条件
PL/SQL_BLOCK | CALL procedure_name;

触发器创建语法的分析：

（1）CREATE为创建的关键字，[]括起的部分是可以选择的，例如OR REPLACE只有在需要替换原有触发器时，需要加上，

否则，将会先删掉原有触发器，创建同名触发器；

（2）BEFORE和AFTER指明触发器的触发时间，事件执行之前或之后触发触发器；

（3）FOR EACH ROW指明触发频率，当定义成行触发器时，对每行符号要求的数据均激活一次触发器，

语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。省略FOR EACH ROW 选项时，

BEFORE和AFTER触发器为语句触发器，替代触发器（INSTEAD OF）只能是行级触发器。

（4）REFERENCING子句说明相关名称，在行触发器的 PL/SQL 块和 WHEN 子句中可以使用相关名称参
照当前的新、旧列值，默认的相关名称分别为 OLD 和 NEW。

触发器的 PL/SQL 块中应用相关名称时，必须在它们之前加冒号(:)，但在 WHEN 子句中则不能加冒号。

（5）WHEN 子句说明触发约束条件。

每个表最多可以建立12种触发器，主要由INSERT,UPDATE,DELETE与触发时间BEFORE,AFTER组合而成：

--一个表最多建立的12个触发器
BEFORE INSERT--插入前（语句级）
BEFORE INSERT FOR EACHE ROW --插入前（行级）
AFTER INSERT --插入后（语句级）
AFTER INSERT FOR EACHE ROW --插入后（行级）

BEFORE UPDATE--更新前（语句级）
BEFORE UPDATE FOR EACHE ROW --更新前（行级）
AFTER UPDATE --更新后（语句级）
AFTER UPDATE FOR EACHE ROW --更新后（行级）

BEFORE DELETE--删除前（语句级）
BEFORE DELETE FOR EACHE ROW --删除前（行级）
AFTER DELETE --删除后（语句级）
AFTER DELETE FOR EACHE ROW --删除后（行级）

触发器触发次序：

1. 执行BEFORE语句级触发器；
2. 对与受语句影响的每一行：
   执行 BEFORE 行级触发器；
   执行 DML 语句；
   执行 AFTER 行级触发器；
3. 执行 AFTER 语句级触发器；

二，DML触发器实例

注意：

  :NEW 修饰符访问操作完成后列的值

  :OLD 修饰符访问操作完成前列的值

实例1：

--创建触发器
CREATE OR REPLACE TRIGGER tri_test
BEFORE DELETE--触发时间，在删除时间执行之前
ON mytest--触发对象
FOR EACH ROW--行级触发器
BEGIN--执行部分
  DBMS_OUTPUT.PUT_LINE('删除之前执行这段代码');
  DBMS_OUTPUT.PUT_LINE(:old.name);--:OLD 修饰符访问操作完成前列的值
END;

在命令窗口中测试触发器：

实例2：

--限制对表操作日期
CREATE OR REPLACE TRIGGER tri_test
BEFORE INSERT OR UPDATE OR DELETE
ON mytest
BEGIN
  IF (to_char(SYSDATE,'HH24:MI')) NOT BETWEEN '09:00' AND '18:00' THEN
     RAISE_APPLICATION_ERROR(-20001,'大伙还没上班，别动mytest表');
  END IF;
END;

命令窗口执行：

实例3：

--创建触发器
CREATE OR REPLACE TRIGGER tri_test
BEFORE UPDATE OF age,NAME OR DELETE--触发事件
ON mytest--触发对象
FOR EACH ROW--行级触发器
WHEN (old.id = 1)--when触发条件：当id=1时触发
BEGIN
  dbms_output.put_line('触发器开始执行,执行相关逻辑');
EXCEPTION 
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('触发器运行异常');
END;

三，INSTEAD OF(替代触发器)实例

创建触发器语法：

--特别注意，替代触发器是针对视图的
CREATE OR REPLACE TRIGGER trigger_name
INSTEAD OF
{INSERT|UPDATE|DELETE[OF column,....]}
[OR {INSERT|UPDATE|DELETE}[OF column,...]]
ON [SCHEMA.]view_name--这个地方只能定义视图
[REFERENCING {OLD [AS] OLD|NEW [AS] NEW| PARENT [AS] PARENT}]
[FOR EACH ROW]
[WHEN conditon]

使用实例1：

第一步，创建视图：

CREATE OR REPLACE VIEW view_test 
AS SELECT * FROM mytest;

第二步，替代触发器使用：

CREATE OR REPLACE TRIGGER tri_inst_test
INSTEAD OF DELETE ON view_test
FOR EACH ROW --只能是行级的
BEGIN
  DBMS_OUTPUT.PUT_LINE('替代触发器开始执行了');
  DELETE FROM mytest WHERE id = :old.id;
END tri_inst_test;

四，系统触发器实例

CREATE OR REPLACE TRIGGER [sachema.]trigger_name
{BEFORE|AFTER}
{ddl_event_list|database_event_list}
ON {DATABASE|[schema.]SCHEMA}
[WHENcondition]

注意：

ddl_event_list：一个或多个DDL时间，多个用OR隔开；

databa_event_list：一个或多个数据库事件，多个用OR隔开；

用户要在系统中创建系统触发器，需要有管理数据库触发器的权限。

系统触发器的种类和事件出现的时机（前或后）：

实例1：

CREATE OR REPLACE TRIGGER tri_ddl
AFTER DDL ON SCHEMA
BEGIN
  DBMS_OUTPUT.PUT_LINE('系统级触发器开始执行处理逻辑');
END tri_ddl;

实例2,数据库登录或退出时，插入两条测试数据实例：

--登录触发器
CREATE OR REPLACE TRIGGER tri_login
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO mytest VALUES(4,'登录'，18);
END tri_login;

--退出触发器
CREATE OR REPLACE TRIGGER tri_loginoff
BEFORE LOGOFF ON DATABASE
BEGIN
  INSERT INTO mytest VALUES(5,'退出',24);
END tri_loginoff;

将数据库退出，登录，然后看看mytest中的数据状况，看触发器是否创建成功：

根据结果，能知道我们创建的系统触发器成功。

总结：触发器有很多细节，需要在实践中多留心细节。先懂而后深，先广而后专。