plsql - 为什么我的“插入后”触发器在创建触发器后不考虑所有其他插入？

Question

下面是概念模型： 在驱动表中，totalTripMade 属性全部为空，但行程表包含了驱动程序进行的所有行程，所以我需要创建一个触发器，这样当一条记录插入到行程中时，触发器将通过使用 l# 计算所有行程来更新 totalTripMade

create or replace trigger updateTrip
   AFTER INSERT 
     on TRIP
     for each row
      declare
         PRAGMA AUTONOMOUS_TRANSACTION;
         totalTrips number;
      begin

     UPDATE DRIVER 
     SET TOTALTRIPMADE = (SELECT COUNT(L#) FROM TRIP WHERE L# =:NEW.L#)
     WHERE L#=:NEW.L#;

         SELECT COUNT(L#) into totalTrips FROM TRIP WHERE L# =:NEW.L#;
         DBMS_OUTPUT.PUT_LINE(:NEW.L# || ' ' || totalTrips);
   commit;
end;
/

SHOW ERRORS;

select * from DRIVER;
--
insert into TRIP values(109, 10001, 'SST005', sysdate );
insert into TRIP values(110, 10001, 'SST005', sysdate );
insert into TRIP values(111, 10001, 'SST005', sysdate );


--
select * from DRIVER;
select * from TRIP;

因此驱动程序 10001 在这 3 个插入语句之前进行了 27 次行程，因为基于 l# 10001 有 27 条记录，但在每次插入之后，此代码“DBMS_OUTPUT.PUT_LINE(:NEW.L# || ' ' || totalTrips) ;" 一直给我 27 3 次，应该是 28、29 然后 30，我可以知道为什么我的触发器没有正确计算行程表中基于 l# 的记录吗？

这是运行这些插入语句后的输出：

SQL> insert into TRIP values(109, 10001, 'SST005', sysdate );
10001 27

1 row created.

SQL> insert into TRIP values(110, 10001, 'SST005', sysdate );
10001 27

1 row created.

SQL> insert into TRIP values(111, 10001, 'SST005', sysdate );
10001 27

1 row created.

标签： plsqltriggerssqlplusdatabase-trigger

Answer 1

我认为错误在这里：

我需要创建一个触发器，以便在将记录插入行程时，触发器将通过使用 l# 计算所有行程来更新 totalTripMade

我不认为你需要创建这样的触发器。如果您需要计算司机的总行程次数，请直接使用类似的查询行程表SELECT COUNT(*) FROM TRIP WHERE L# = <driver_number>。即使您有很多行，只要您在L#列上有索引，它也不会减慢数据库速度。

使用这样的触发器不是一个好主意的原因有很多。首先，如果同时插入多行会发生什么？如果插入 10 行，触发器应该触发 10 次，还是最后只触发一次？

另外，如果您更新TRIP表中的一行以更改驱动程序编号，或删除此表中的一行，会发生什么情况？你在处理这些案件吗？

但是你不应该做这样的事情的最大原因是它破坏了并发性。假设一名司机在数据库中记录了 27 次行程。假设您正试图向该司机添加三个行程，并且其他人正试图同时向同一个司机添加三个不同的行程。在您提交之前，你们都不能看到彼此的行程，你们都只能看到已经存在的 27 次行程。在您的会话中，触发器会尝试将此驱动程序的总行程设置为 30，因为您的会话可以看到 27 个现有行程和您的三个新行程，并且在其他人的会话中，触发器还将尝试设置总数该司机的行程也增加到 30 次，因为本次会议看到了 27 次现有行程和他们的三个新行程，但不是你的三个新旅行。一旦你都提交了，驱动程序在数据库中就有 33 次行程，但它们totalTripsMade错误地显示为 30。

回答你的问题

我可以知道为什么我的触发器没有正确计算行程表中基于 l# 的记录吗？

我猜这是因为在处理这个触发器时你遇到了一个ORA-04091 table is mutating错误，看到这个错误的谷歌搜索结果说使用PRAGMA AUTONOMOUS_TRANSACTION，所以你将它添加到你的触发器中并且错误消失了。此 pragma 导致您的触发器在独立会话中运行：它看不到您插入的行程，因此打印 27 因为它只能看到已经存在的行程。

复合触发器是一种ORA-04091无需使用即可避免错误的方法PRAGMA AUTONOMOUS_TRANSACTION。这里有一个使用复合触发器的例子。但是，您还必须为UPDATEand编写类似的触发器DELETE，并且复合触发器无法解决我上面提到的并发问题。

但是，我建议去掉这个触发器（也可能还有表中的totalTripMade列DRIVER）。如果您需要计算司机的行程次数，请通过计算TRIP表中的行数来完成。要求你删除你的代码可能会让我觉得很苛刻，但最终你是在尝试做一些行不通的事情。我在 StackOverflow 上看到了人们尝试做类似事情的其他问题，所以你并不孤单尝试这样的事情。在直观的层面上，将这些计数添加到表中似乎是一件合理的事情。但是，一旦您了解了数据库的工作原理，就会发现其中的缺陷。