Oracle语句级触发器.docx

语句级触发器我们先看一个AFTER-INSERT-STATEMENT触发器：CREATE OR REPLACE TRIGGER temp_aisAFTER INSERT ON TEMPBEGINdbms_output.put_line(executing temp_ais);END;看一下下面语句的结果：SQL set feedback offSQL INSERT INTO temp VALUES (1); - insert 1 rowexecuting temp_aisSQL INSERT INTO temp VALUES (1); - insert 1 rowexecuting temp_aisSQL INSERT INTO temp SELECT * FROM temp; - insert 2 rowsexecuting temp_ais每个SQL插入语句将触发一次，行级触发器最后一条语句要触发两次。一、事件顺序用Insert 语句级触发器可以做：可以在表上执行一个合计运算，可以在insert前或后来计算。可以使用语句级触发器去处理行级触发器控制的数据。可以给事件发信号。可以仅仅是一个打印语句。也可以是一个email或使用DBMS_ALERT包向其他处理过程发送信号。Tasks PerformedStages - | 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_nameAFTER | BEFORE INSERT ON table_nameDECLARELocal declarationsBEGINBody written PL/SQLEND;语句级和行级触发器在语法上关键的不同在于：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_biudsBEFORE INSERT OR UPDATE OR DELETE ON TEMPBEGINCASEWHEN inserting THENPL/SQL code hereWHEN updating THENPL/SQL code hereWHEN deleting THENPL/SQL code hereEND CASE;END;三、语句级组合使用错误码来更新Errors包包商业规则逻辑放到一个约束包中编写before或after语句级触发器1）、第一步是声明错误码和错误信息。Errors包更新包含了-20002和-2003两个错误码CREATE OR REPLACE PACKAGE errors ISeng_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 ISPROCEDURE constrain_budget(limit NUMBER,err_code PLS_INTEGER,err_textVARCHAR2);END professors_cons;CREATE OR REPLACE PACKAGE BODY professors_cons ISPROCEDURE constrain_budget(limit NUMBER,err_code PLS_INTEGER,err_textVARCHAR2)ISbudget_sum NUMBER;BEGINSELECT SUM(salary) INTO budget_sum FROMprofessors;IF budget_sum limit THENRAISE_APPLICATION_ERROR(err_code, err_text);END IF;END constrain_budget;END professors_cons;3）、定义before和after触发器CREATE OR REPLACE TRIGGER professors_bisBEFORE INSERT OR UPDATE ON professorsBEGINprofessors_cons.constrain_budget(55000, errors.budget_err_1,errors.budget_err_1_txt);END;CREATE OR REPLACE TRIGGER professors_aisAFTER INSERT OR UPDATE ON professorsBEGINprofessors_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 ISPROCEDURE load_temp_table(v_prof_name f_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 ISPROCEDURE load_temp_table(v_prof_name f_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)ISBEGININSERT 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 ISBEGINFOR rec in (SELECT * FROM professors_g) LOOPdbms_output.put_line(f_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_adrAFTER DELETE ON professorsFOR EACH ROWBEGINprofessors_cons.load_temp_table(:f_name, :old.specialty, :old.hire_date,:old.salary, :old.tenure, :old.department);END;下一个是after delete语句级触发器，使用约束包打印删除的行信息。CREATE OR REPLACE TRIGGER professors_adsAFTER DELETE ON professorsBEGINprofessors_cons.dump_temp_table;END;delete SQL语句后面是语句级触发器的输出：SQL DELETE FROM professors;Blake Mathe