Oracle触发器介绍 语句级触发器


语句级触发器

  我们先看一个AFTER-INSERT-STATEMENT触发器:

  CREATE OR REPLACE TRIGGER temp_ais

  AFTER INSERT ON TEMP

  BEGIN

  dbms_output.put_line('executing temp_ais');

  END;

  看一下下面语句的结果:

  SQL> set feedback off

  SQL> INSERT INTO temp VALUES (1);          -- insert 1 row

  executing temp_ais

  SQL> INSERT INTO temp VALUES (1);          -- insert 1 row

  executing temp_ais

  SQL> INSERT INTO temp SELECT * FROM temp; -- insert 2 rows

  executing temp_ais

  每个SQL插入语句将触发一次,行级触发器最后一条语句要触发两次。

  一、事件顺序

  用Insert 语句级触发器可以做:

  ·可以在表上执行一个合计运算,可以在insert前或后来计算。

  ·可以使用语句级触发器去处理行级触发器控制的数据。

  ·可以给事件发信号。可以仅仅是一个打印语句。也可以是一个email或使用DBMS_ALERT包向其他处理过程发送信号。

  Tasks Performed

  Stages                                          -------------------

  ---------------------------------- ———>   |   Rehect the     |

  Fires once          |   BIS function                     |                   |   Transaction    |

  per statement    | Statement Level Trigger |                     -------------------

  ----------------------------------- ———>     -------------------

  |                                                      | Take Action     |

  |                                                       -------------------

  insert                            |

  3行记录        |    --------------------------                   |

  ————> |    |    Row Trigger       |                  |    -------------------------------

  ————> |    --------------------------                   |    |每一个行触发器插入一|

  ————> |                |                                         |    |行触发一次,插入三行|

  | ----------------------------------------   |    |触发三次,语句级触发|

  | | Oracle enforces constraints | |    |器在行操作和行数据上|

  | ----------------------------------------   |    |不可见                            |

  |                |                                         |    |                                         |

  |    --------------------------                    |    -------------------------------

  |    |    Row Trigger          |                |

  |    --------------------------                    |

  |

  |                                                         ----------------------

  ---------------------------------- ————> |   Rehect the     |

  Fires once          |   AIS function                     |                      |   Transaction   |

  per statement    | Statement Level Trigger |                       ---------------------

  ---------------------------------- ————>   --------------------

  | Take Action |

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

  上图显示了语句级出发器的行为。同时也显示了在Before statement触发器和After statement触发器间的行级触发器的触发情况。如果一个update SQL语句更新三行,那么行级触发器触发三次,语句级触发器触发一次。

  二、insert 语句级触发器定义语法

  语法如下:

  CREATE OR REPLACE TRIGGER trigger_name

  [AFTER | BEFORE] INSERT ON table_name

  DECLARE

  Local declarations

  BEGIN

  Body written PL/SQL

  END;

  语句级和行级触发器在语法上关键的不同在于:FOR EACH ROW字句。在行级触发器中指定这个子句而语句级触发器中不需要指定。

  1)、WHEN(Boolean expression) 所有行触发器可用

  2)、OF column_name clause     仅对update触发器可用

  在语句级触发器中:

  ·引用:NEW.COLUMN_NAME and :OLD.COLUMN_NAME是不正确的。

  ·不能使用When(boolean expression)子句中包含OLD.COLUMN_NAME和 NEW.COLUMN_NAME.

  可以使用下面的语句:

  CREATE OR REPLACE TRIGGER temp_biuds

  BEFORE INSERT OR UPDATE OR DELETE ON TEMP

  BEGIN

  CASE

  WHEN inserting THEN

  PL/SQL code here

  WHEN updating THEN

  PL/SQL code here

  WHEN deleting THEN

  PL/SQL code here

  END CASE;

  END;

  三、语句级组合

  ·使用错误码来更新Errors包

  ·包商业规则逻辑放到一个约束包中

  ·编写before或after语句级触发器

  1)、第一步是声明错误码和错误信息。Errors包更新包含了-20002和-2003两个错误码

  CREATE OR REPLACE PACKAGE errors IS

  eng_dept_sal CONSTANT PLS_INTEGER := -20001;

  app_error_02 CONSTANT PLS_INTEGER := -20002;

  app_error_03 CONSTANT PLS_INTEGER := -20003;

  eng_dept_sal_txt CONSTANT VARCHAR2(100) :=

  'The salary exceeds the ENGL maximum of $10,000.00';

  app_error_02_txt CONSTANT VARCHAR2(100) :=

  'No additions if the budget exceeds $55,000.00';

  app_error_03_txt CONSTANT VARCHAR2(100) :=

  'Budget cannot be over $60,000.00';

  END errors;

  2)、把商业逻辑封装在约束包中。

  CREATE OR REPLACE PACKAGE professors_cons IS

  PROCEDURE constrain_budget

  (limit NUMBER,err_code PLS_INTEGER,err_text

  VARCHAR2);

  END professors_cons;

  CREATE OR REPLACE PACKAGE BODY professors_cons IS

  PROCEDURE constrain_budget

  (limit NUMBER,err_code PLS_INTEGER,err_text

  VARCHAR2)

  IS

  budget_sum NUMBER;

  BEGIN

  SELECT SUM(salary) INTO budget_sum FROM

  professors;

  IF budget_sum > limit THEN

  RAISE_APPLICATION_ERROR(err_code, err_text);

  END IF;

  END constrain_budget;

  END professors_cons;

  3)、定义before和after触发器

  CREATE OR REPLACE TRIGGER professors_bis

  BEFORE INSERT OR UPDATE ON professors

  BEGIN

  professors_cons.constrain_budget

  (55000, errors.budget_err_1,

  errors.budget_err_1_txt);

  END;

  CREATE OR REPLACE TRIGGER professors_ais

  AFTER INSERT OR UPDATE ON professors

  BEGIN

  professors_cons.constrain_budget

  (60000, errors.budget_err_2,

  errors.budget_err_2_txt);

  END;
四、处理行获得的数据

  行级触发器可以在全局临时表中存储:OLD 和 :NEW 字段值。全局临时表范围仅是事务。通过复制:OLD 和 :NEW 值,商业规则的处理被延期到语句级触发器上。有时是必须的,因为商业规则是复杂的,需要从表中查询,包括表被更新。

  1)、首先需要一个全局临时表,它在行级触发器上用于存储数据。

  CREATE global temporary TABLE professors_g

  (prof_name     VARCHAR2(10),

  specialty     VARCHAR2(20),

  hire_date     DATE,

  salary        NUMBER(7,2),

  tenure        VARCHAR2(3),

  department    VARCHAR2(10)) ON COMMIT DELETE ROWS;

  2)、为这张表编写存储过程,放于包Professors_cons里.如下:

  CREATE OR REPLACE PACKAGE professors_cons IS

  PROCEDURE load_temp_table

  (v_prof_name professors.prof_name%TYPE,

  v_specialty professors.specialty%TYPE,

  v_hire_date professors.hire_date%TYPE,

  v_salary     professors.salary%TYPE,

  v_tenure     professors.tenure%TYPE,

  v_department professors.department%TYPE);

  PROCEDURE dump_temp_table;

  END professors_cons;

  包体为:

  CREATE OR REPLACE PACKAGE BODY professors_cons IS

  PROCEDURE load_temp_table

  (v_prof_name professors.prof_name%TYPE,

  v_specialty professors.specialty%TYPE,

  v_hire_date professors.hire_date%TYPE,

  v_salary     professors.salary%TYPE,

  v_tenure     professors.tenure%TYPE,

  v_department professors.department%TYPE)

  IS

  BEGIN

  INSERT INTO professors_g VALUES

  (v_prof_name, v_specialty, v_hire_date,

  v_salary, v_tenure, v_department);

  END load_temp_table;

  PROCEDURE dump_temp_table IS

  BEGIN

  FOR rec in (SELECT * FROM professors_g) LOOP

  dbms_output.put_line(

  rec.prof_name||' '||rec.specialty||' '||

  rec.hire_date||' '||rec.salary||' '||

  rec.tenure||' '||rec.department);

  END LOOP;

  END dump_temp_table;

  END professors_cons;

  3)、下面是一个after delete 行触发器。当它触发时,通过Professors_cons插入临时表一行数据。

  CREATE OR REPLACE TRIGGER professors_adr

  AFTER DELETE ON professors

  FOR EACH ROW

  BEGIN

  professors_cons.load_temp_table

  (:old.prof_name, :old.specialty, :old.hire_date,

  :old.salary, :old.tenure, :old.department);

  END;

  下一个是after delete语句级触发器,使用约束包打印删除的行信息。

  CREATE OR REPLACE TRIGGER professors_ads

  AFTER DELETE ON professors

  BEGIN

  professors_cons.dump_temp_table;

  END;

  delete SQL语句后面是语句级触发器的输出:

  SQL> DELETE FROM professors;

  Blake Mathematics 08-aug-2003 02:06:27 10000 YES MATH

  Milton Am Hist 09-aug-2003 02:06:27 10000 YES HIST

  Wilson English 06-aug-2003 02:06:27 10000 YES ENGL

  Jones Euro Hist 12-jul-2003 02:06:28 10000 YES HIST

  Crump Ancient Hist 12-jul-2003 02:06:28 10000 YES HIST

  5 rows deleted


« 
» 
快速导航

Copyright © 2016 phpStudy | 豫ICP备2021030365号-3