oracle触发器列级触发,Oracle触发器介绍语句级触发器

最新推荐文章于 2023-04-07 16:28:20 发布

GA.M

最新推荐文章于 2023-04-07 16:28:20 发布

阅读量254

点赞数

文章标签： 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 | | -------------------------------

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

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

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

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

| | | | |

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

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

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)

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.

GA.M

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle触发器列级触发,Oracle触发器介绍语句级触发器

语句级触发器我们先看一个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 t...
复制链接

扫一扫