更新触发器 PL/SQL Oracle [英] Update Trigger PL/SQL Oracle

查看：38
本文介绍了更新触发器 PL/SQL Oracle的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个具有以下结构的表:

I have a table with the following structure: 

create table treballa (
code varchar2(4), 
 name varchar2(20), 
 director varchar2(4), 
department number, 
salary int,
 primary key (code), 
 foreign key (director) references treballa(code) 
)

我需要创建 1 个触发器来检查更新工人的部门编号，该部门所有工人之间的总工资是否不超过 10000，但我真的不知道该怎么做... 请你帮助我好吗?非常感谢.

And I need to create 1 trigger that checks if the department number of the updated worker, the total salary between all the workers of that department doesn't exceeds 10000, but I don't know how to do it really... Could you please help me? Thank you very much.

CREATE OR REPLACE TRIGGER controlsalaridept BEFORE INSERT OR UPDATE ON TREBALLA
FOR EACH ROW
  DECLARE
    salaritotal INT := 0;
  BEGIN
    IF INSERTING THEN
      SELECT sum(salary) INTO salaritotal FROM TREBALLA WHERE DEPARTMENT LIKE :new.DEPARTAMENT;
      DBMS_OUTPUT.PUT_LINE('Salari Total abans suma:'||salaritotal);
      salaritotal := salaritotal + :new.SALARY;
      DBMS_OUTPUT.PUT_LINE('Salari Total després:'||salaritotal);
      IF salaritotal > 10000 THEN
        raise_application_error(-20025, 'La suma del salari total de cada departament supera els 10000 Euros');
      END IF;
    END IF;
    IF UPDATING THEN
      SELECT sum(salary) INTO salaritotal FROM TREBALLA WHERE DEPARTMENT LIKE :old.DEPARTAMENT;
      DBMS_OUTPUT.PUT_LINE('Salari Total abans suma:'||salaritotal);
      salaritotal := salaritotal - :old.SALARY + :new.SALARY;
      DBMS_OUTPUT.PUT_LINE('Salari Total després:'||salaritotal);
      IF salaritotal > 10000 THEN
        raise_application_error(-20026,'La suma del salari total de cada departament supera els 10000 Euros');
      END IF;
    END IF;
  END;

结束编辑/

更新时出错:

[42000][4091] ORA-04091:表 SPECIAL.TREBALLA 正在变异，触发器/函数可能看不到它 ORA-06512: 在SPECIAL.CONTROLSALARIDEPT"，第 14 行 ORA-04088:执行期间出错触发器 'SPECIAL.CONTROLSALARIDEPT'

[42000][4091] ORA-04091: table SPECIAL.TREBALLA is mutating, trigger/function may not see it ORA-06512: at "SPECIAL.CONTROLSALARIDEPT", line 14 ORA-04088: error during execution of trigger 'SPECIAL.CONTROLSALARIDEPT'

PD:抱歉，我是 oracle 的新手，我需要关于这个触发器的帮助，我不知道我在做什么.... 触发器的第一部分IF INSERTING"效果很好，问题在于更新...

PD: Sorry, I'm very new on oracle and I need help with this trigger, I don't know even if it's right what I'm doing.... The first part of the trigger "IF INSERTING" works well, the problem is with the UPDATING...

推荐答案

尝试复合触发器:

CREATE OR REPLACE TRIGGER compound_trigger_name
FOR  INSERT OR UPDATE OF salary ON treballa
COMPOUND TRIGGER

  TYPE Departments_t   IS TABLE OF treballa.department%TYPE INDEX BY varchar2(100);
  Departments          Departments_t;

     BEFORE EACH ROW IS
     BEGIN
        -- collect updated or inserted departments 
        Departments( :new.department ) := :new.department;
     END BEFORE EACH ROW;

     AFTER STATEMENT IS
        sum_sal NUMBER;
     BEGIN
      -- for each updated department check the restriction
      FOR dept IN Departments.FIRST .. Departments.LAST
      LOOP
         SELECT sum(salary) INTO sum_sal FROM treballa WHERE department = dept;
         IF sum_sal > 1000 THEN
            raise_application_error(-20123, 'The total salary for department '||dept||' cannot exceed 1000');
         END IF;
      END LOOP;
     END AFTER STATEMENT;

END compound_trigger_name;
/

<小时>

========编辑 - 一些问题和答案 ============

问:为什么会发生变异表错误?
答:文档中对此进行了描述:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#g1699708

Q: Why a mutating table error occurs ?
 A: This is described in the documentation:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#g1699708

对变异表的触发限制
变异表是一个表正在被 UPDATE、DELETE 或 INSERT 语句修改，或可能因 DELETE CASCADE 的影响而更新的表约束.

Trigger Restrictions on Mutating Tables
A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.

发出触发语句的会话无法查询或修改变异表.此限制可防止触发器看到一组不一致的数据.

The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.

此限制适用于所有使用 FOR EACH ROW 的触发器条款.不考虑在 INSTEAD OF 触发器中修改的视图变异.

This restriction applies to all triggers that use the FOR EACH ROW clause. Views being modified in INSTEAD OF triggers are not considered mutating.

当触发器遇到变异表时，会发生运行时错误，触发体和触发语句的效果滚动返回，并将控制权返回给用户或应用程序.(您可以使用复合触发器以避免变异表错误.更多信息，请参阅使用复合触发器避免突变表错误.)

When a trigger encounters a mutating table, a run-time error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application. (You can use compound triggers to avoid the mutating-table error. For more information, see Using Compound Triggers to Avoid Mutating-Table Error.)

<小时>

问:如何避免变异表错误?
A:文档推荐使用复合触发器，参见:http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#CHDFEBFJ

Q: how to avoid a mutating table error ?
 A: The documentation recommends the use of a coumpound trigger, see this: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#CHDFEBFJ

使用复合触发器避免突变表错误您可以使用复合触发器以避免变异表错误 (ORA-04091)更改表的触发器限制中描述.

Using Compound Triggers to Avoid Mutating-Table Error You can use compound triggers to avoid the mutating-table error (ORA-04091) described in Trigger Restrictions on Mutating Tables.

<小时>

问:什么是复合触发器，它是如何工作的?
A:这是一个很大的话题，请参考这里的文档:http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHEFGFD

简而言之:这是一种特殊的触发器，可以将四种类型的单独触发器组合在一起:BEFORE statement、BEFORE-for each row、AFTER for每一行 和AFTER statament 合并成一个声明.它可以更轻松地实现某些需要将一些数据从一个触发器传递到另一个触发器的场景.请研究以上链接了解更多详情.

Q: What is a compound trigger and how does it work ?
 A: This is a huge topic, please refer to the documentation here: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHEFGFD

In short: this is a special kind of a trigger that makes psiible to combine four types of separate triggers: BEFORE statement, BEFORE-for each row, AFTER for each row and AFTER statament into a one declaration. It makes it easier to implement some scenarious in which there is a need to pass some data from one trigger to another one. Please study the above link for more details.

问:但是 "Departments( :new.department ) := :new.department; 实际上是什么?
A:这个声明将一个部门编号存储到一个关联数组中.

这个数组在复合触发器的声明部分声明:

Q: But what actually does "Departments( :new.department ) := :new.department; ?
A: This declaration stores a department number into an associative array.

This array is declared in a declarative part of the compound trigger:

  TYPE Departments_t   IS TABLE OF treballa.department%TYPE INDEX BY varchar2(100);
  Departments          Departments_t;

与复合触发器相关的文档说:http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHJBEFE

The documentation related to the compound triggers says that: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHJBEFE

可选的声明部分(第一部分)声明变量和时间点部分可以使用的子程序.当触发器触发，声明部分在任何时间点部分之前执行执行.本节中声明的变量和子程序具有触发语句持续时间.

The optional declarative part (the first part) declares variables and subprograms that timing-point sections can use. When the trigger fires, the declarative part executes before any timing-point sections execute. Variables and subprograms declared in this section have firing-statement duration.

以上意味着 Departments 变量仅在整个处理开始时初始化一次，就在触发器触发之后.触发语句持续时间"表示该变量在触发器完成后销毁.

该语句: Departments( :new.department ) := :new.department; 在关联数组中存储部门编号.它位于 BEFORE EACH ROW 部分，然后针对更新/插入语句更新(或插入)的每一行执行它.

:new 和 :old 是伪记录，更多关于它们的信息你可以在这里找到:http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS99955
在简短: :new.department 检索 department 列的新值 - 对于当前更新的行(更新值 - 更新后)，而 :old.department 给出该列的旧值(更新前).

此集合稍后用于 AFTER STATEMENT，当触发器选择所有更新的部门(在 FOR-LOOP 中)时，为每个部门触发 SELECT SUM(salary) ... 然后检查这个总和是否小于 1000

考虑一个简单的更新:UPDATE treballa SET 工资 = 工资 + 10.这是一个更新语句，但一次更改了许多行.我们的触发器的执行顺序如下:

The above means that Departments variable is initialized only once at the beginning of the whole processing, just after the trigger fires. "Firing-statement duration" means that this variable is destroyed after the trigger finishes.

This statement: Departments( :new.department ) := :new.department; stores a department number in the associative array. It is in BEFORE EACH ROW section, then it is executed for each row that is updated (or inserted) by the update/insert statement.

:new and :old are pseudorecords, more on them you can find here: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS99955
In short: :new.department retrieves a new value of department column- for a currently updated row (updated value - AFTER the update), while :old.department gives an old value of this column (BEFORE the update).

This collection is later used in the AFTER STATEMENT, when the trigers pick all updated departments (in a FOR-LOOP), for each departmens fires SELECT SUM(salary) ... and then checks if this sum is less than 1000

Consider a simple update: UPDATE treballa SET salary = salary + 10. This is a single update statement, but changes many rows at once. The order of execution of our trigger is as follows:

  1. 更新状态被触发:UPDATE treballa SET工资=工资+ 10
  2. 执行触发器的声明部分，即:Departments变量被初始化
  3. BEFORE EACH ROW 部分针对每个更新的行单独执行 - 与要更新的行一样多.在这个地方，我们从更改的行中收集所有部门.
  4. AFTER STATEMENT 部分被执行.此时表已经更新 - 所有行都已经有新的、更新的工资.我们循环遍历保存在 Departments 中的部门，并检查每个部门的工资总和是否小于或等于 1000.如果这些部门中的任何一个的总和 > 1000，则抛出错误，并且整个更新被中止并回滚.否则触发器完成，更新完成(但无论如何您都需要提交这些更改).
  1. The update statament is fired: UPDATE treballa SET salary = salary + 10
  2. The declarative section of the trigger is executed, that is: Departments variable is initialized
  3. BEFORE EACH ROW section is executed, separately for each updated row - as many times as there are rows to be updated. In this place we collect all departments from changed rows.
  4. AFTER STATEMENT section is executed. At this point the table is already updated - all rows already have new, updated salaries. We loop throught departments saved in Departments and for each one we check if sum of salaries is less or equal to 1000. If this sum is > 1000 for any of these departments, then an error is thrown, and the whole update is aborted and rolled back. Otherwise the trigger finishes, and the update is done (but you need to commit these changes anyways).

<小时>

问:什么是关联数组，为什么只使用这种集合，而不使用其他集合(变量数组或嵌套表)?
A:PL/SQL 集合是一个很大的话题.点击此链接了解它们:http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS005

简而言之 - 关联数组(或索引表)就像 Java 中的地图(哈希图、树图等) - 它是一组键值对，并且每个键都是唯一的.您可以将相同的键多次放入该数组(具有不同的值)，但该键只会存储一次 - 它是唯一的.
我用它来获得一组独特的部门.
再次考虑我们的更新示例:UPDATE treballa SETsalary =salary + 10 - 此命令涉及具有相同部门的数百行.我不希望同一部门的集合重复 100 次，我需要一组唯一的部门，并且我想为每个部门只执行一次查询 SELECT sum()...，不是 100 次.在 sssociative 数组的帮助下，它会自动完成 - 我得到一组独特的 deparments.

Q: What is an associative array, and why just this kind of collection is used, rather than other collections (a varray or a nested table) ?
A: PL/SQL collections are a huge topic. Follow this link to learn them: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS005

In short - Associative array (or index-by table) is like a map in java (hashmap, treemap etc) - it is a set of key-value pairs, and each key is unique. You can put the same key many times to this array (with different values), but this key will be stored only once - it is unique.
I've used it to get unique set of departments.
Consider our update example again: UPDATE treballa SET salary = salary + 10 - this command touches hundreds of rows that have the same department. I don't want a collection with the same department duplicated 100 times, I need an unique set of departments, and I want to execute our query SELECT sum()... only once for each department, not 100 times. With the help of the sssociative array it is done automatically - I get unique set of deparments.

这篇关于更新触发器 PL/SQL Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆