Oracle PL/SQL 触发器更新另一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10045611/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle PL/SQL Trigger to update another column
提问by VGe0rge
I am trying to create a trigger that updates another table with PL/SQL and I am having some problems. (I have read thisbut doesn't help a lot).
我正在尝试创建一个使用 PL/SQL 更新另一个表的触发器，但我遇到了一些问题。（我已经读过这个，但没有多大帮助）。
Here is my situation, I have lets say 2 tables :
这是我的情况，我可以说 2 个表：
CustomersTable
客户表
CustomerIDnumber Primary Key, ItemsDeliverednumber
CustomerIDnumber 主键，ItemsDeliverednumber
ItemsTable
项目表
CustomerIDnumber, ItemIDnumber, ItemDeliveredVarchar(15)
CustomerID号、ItemID号、ItemDeliveredVarchar(15)
Lets say that when somenone places an order we have a new record at Items table that looks like this:
假设当有人下订单时，我们在 Items 表中有一条新记录，如下所示：
| CustomerID | ItemID | ItemDelivered |
| 1 | 1 | False |
I want a trigger that will raise the ItemsDelivered counter whenever someone updates the ItemDelivered collumn to "True".
我想要一个触发器，当有人将 ItemDelivered 列更新为“True”时，它会引发 ItemsDelivered 计数器。
create or replace Trigger UpdateDelivered
After Update On Items For
Each Row
Declare
Counter Customers.ItemsDelivered%Type;
Begin
If (:Old.ItemDelivered ='False' And :New.ItemDelivered='True') Then
Select ItemsDelivered into Counter From Customers where CustomerdID =:New.CustomerID;
Update....
end if;
END;
Here is my problem, if only the ItemDelivered column is updated there is no New.CustomerID!
这是我的问题，如果仅更新 ItemDelivered 列，则没有 New.CustomerID！
Is there any way to get the CustomerID of the row that have just updated? (I have tried to join with inserted virtual table but I am getting an error that the table doesn't exists)
有没有办法获取刚刚更新的行的CustomerID？（我试图加入插入的虚拟表，但我收到一个表不存在的错误）
回答by Justin Cave
In a row-level trigger on an UPDATE, both :new.customerIDand :old.customerIDshould be defined. And unless you're updating the CustomerID, the two will have the same value. Given that, it sounds like you want
在 上的行级触发器中UPDATE，:new.customerID和:old.customerID都应该被定义。除非您更新CustomerID，否则两者将具有相同的值。鉴于此，听起来你想要
create or replace Trigger UpdateDelivered
After Update On Items For
Each Row
Begin
If (:Old.ItemDelivered ='False' And :New.ItemDelivered='True') Then
Update Customers
set itemsDelivered = itemsDelivered + 1
where customerID = :new.customerID;
end if;
END;
That being said, however, storing this sort of counter and maintaining it with a trigger is generally a problematic way to design a data model. It violates basic normalization and it potentially leads to all sorts of race conditions. For example, if you code the trigger the way you were showing initially where you do a SELECTto get the original count and then do an update, you'll introduce bugs in a multi-user environment because someone else could also be in the process of marking an item delivered and neither transaction would see the other session's changes and your counter would get set to the wrong value. And even if you implement bug-free code, you've got to introduce a serialization mechanism (in this case the row-level lock on the CUSTOMERStable taken out by the UPDATE) that causes different sessions to have to wait on each other-- that is going to limit the scalability and performance of your application.
然而，话虽如此，存储这种计数器并用触发器维护它通常是设计数据模型的一种有问题的方法。它违反了基本的规范化，并可能导致各种竞争条件。例如，如果您按照最初显示的方式对触发器进行编码SELECT以获取原始计数，然后进行更新，则会在多用户环境中引入错误，因为其他人也可能正在处理标记已交付的项目，并且两个事务都不会看到其他会话的更改，并且您的计数器将设置为错误的值。即使您实现了无错误代码，您也必须引入序列化机制（在这种情况下，CUSTOMERS表上的行级锁由UPDATE) 导致不同的会话必须相互等待——这将限制应用程序的可伸缩性和性能。
To demonstrate that the :old.customerIDand the :new.customerIDwill both be defined and will both be equal
证明 the:old.customerID和 the :new.customerIDwill 都被定义并且都相等
SQL> desc items
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMERID NUMBER
ITEMID NUMBER
ITEMDELIVERED VARCHAR2(10)
SQL> ed
Wrote file afiedt.buf
1 create or replace
2 trigger updateDelivered
3 after update on items
4 for each row
5 begin
6 if( :old.itemDelivered = 'False' and :new.itemDelivered = 'True' )
7 then
8 dbms_output.put_line( 'New CustoerID = ' || :new.customerID );
9 dbms_output.put_line( 'Old CustomerID = ' || :old.customerID );
10 end if;
11* end;
SQL> /
Trigger created.
SQL> select * from items;
CUSTOMERID ITEMID ITEMDELIVE
---------- ---------- ----------
1 1 False
SQL> update items
2 set itemDelivered = 'True'
3 where customerID = 1;
New CustoerID = 1
Old CustomerID = 1
1 row updated.
回答by Big Ed
If you would like to store the item count in the database, I would recommend a pair of triggers. You would use an after row trigger to record the item number (perhaps in a table variable in your package) and an after statement trigger that will actually update the counter, calculating the items delivered directly from the base date. That is, by
如果您想将项目计数存储在数据库中，我会推荐一对触发器。您可以使用 after 行触发器来记录项目编号（可能在您的包中的表变量中）和实际更新计数器的 after 语句触发器，计算从基准日期直接交付的项目。也就是说，由
select sum(itemsDelivered) from Customers where itemId = :itemId;
This way, you avoid the dangers of corrupting the counters, because you are always setting it to what it should be. It's probably a Good Idea to keep the derived data in a separate table.
这样，您就可以避免破坏计数器的危险，因为您总是将其设置为应有的值。将派生数据保存在单独的表中可能是一个好主意。
We built our old system entirely on database triggers which updated data in separate "Derived" tables, and it worked very well. It had the advantage that all of our data manipulation could be performed by inserting, updating and deleting the database tables with no need to know the business rules. For instance, to put a student into a class, you just had to insert a row into the registration table; after your select statement, tuition, fees, financial aid, and everything else were already calculated.
我们完全基于数据库触发器构建了我们的旧系统，这些触发器更新了单独的“派生”表中的数据，并且运行良好。它的优点是我们所有的数据操作都可以通过插入、更新和删除数据库表来执行，而无需了解业务规则。例如，要将一个学生放入一个班级，您只需在注册表中插入一行；在你的选择声明之后，学费、杂费、经济援助和其他一切都已经计算好了。
