更新触发器PL/SQL Oracle [英] Update Trigger 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:在 ORA-04088:第14行中的"SPECIAL.CONTROLSALARIDEPT":执行期间出错 触发"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的新手，我需要有关该触发器的帮助，即使我做的正确，我也不知道....触发器的第一部分如果插入"运作良好，问题出在UPDATING ...

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

---

问:如何避免发生变异表错误?
答:文档建议使用触发触发器，请参见:

---

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.

---

问:什么是复合触发器，它如何工作?
答:这是一个巨大的主题，请参考此处的文档:

---

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;实际上是什么?
答:此声明将部门编号存储到关联数组中.

该数组在复合触发器的声明部分中声明:

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部分中，然后对由update/insert语句更新(或插入)的每一行执行.

:new和:old是伪记录，有关它们的更多信息，请参见这里: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS99955
简而言之::new.department检索新值列-用于当前更新的行(更新的值-更新后)，而:old.department给出此列的旧值(更新前).

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

考虑一个简单的更新:UPDATE treballa SET salary = salary + 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 salary = salary + 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).

---

问:什么是关联数组，为什么只使用这种集合而不是其他集合(varray或嵌套表)?
答:PL/SQL集合是一个巨大的话题.请通过以下链接进行学习: http://docs. oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS005

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

---

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屋！