Oracle - 创建更新另一个表的触发器时出现问题

2021-12-30 00:00:00 sql database oracle11g oracle

我已阅读有关创建触发器的 Oracle 文档，并且正在按照它显示的方式执行操作，但是这不起作用.我的目标是使用 TPM_TRAININGPLAN 表中出现的最小 STARTDATE 更新 TPM_PROJECT 表.因此，每次有人更新 TPM_TRAININGPLAN 中的 STARTDATE 列时，我都想更新 TPM_PROJECT 表.这是我正在尝试的:

I've read the Oracle docs on creating triggers and am doing things exactly how it shows, however this just isn't working. My goal is to update the TPM_PROJECT table with the minimum STARTDATE appearing in the TPM_TRAININGPLAN table. Thus, every time someone updates the STARTDATE column in TPM_TRAININGPLAN, I want to update teh TPM_PROJECT table. Here's what I'm trying:

CREATE TRIGGER Trigger_UpdateTrainingDelivery
    AFTER DELETE OR INSERT OR UPDATE OF STARTDATE
    ON TPM_TRAININGPLAN
    FOR EACH ROW WHEN (new.TRAININGPLANTYPE='prescribed')
    BEGIN
       UPDATE TPM_PROJECT SET TRAININGDELIVERYSTART = (SELECT MIN(TP.STARTDATE) FROM TPM_TRAININGPLAN TP WHERE TP.PROJECTID = new.PROJECTID AND TP.TRAININGPLANTYPE='prescribed')
       WHERE PROJECTID = new.PROJECTID
    END;

触发器创建时没有错误，但我收到警告:

The trigger is created with no errors, but I do get a warning:

 Warnings: ---> 
   W (1): Warning: execution completed with warning
          <---

当然，Oracle 还不够好，无法真正告诉我警告是什么，我只是向我展示了警告.

Of course Oracle isn't nice enough to actually tell me what the warning is, I simply am shown that there is one.

接下来，如果我更新训练计划表:

Next, if I update the training plan table with:

UPDATE TPM_TRAININGPLAN
set STARTDATE = to_date('03/12/2009','mm/dd/yyyy')
where TRAININGPLANID=15916;

我收到错误消息:

>[Error] Script lines: 20-22 ------------------------
 ORA-04098: trigger 'TPMDBO.TRIGGER_UPDATETRAININGDELIVERY' is invalid and failed re-validation
 Script line 20, statement line 1, column 7

知道我做错了什么吗?谢谢！

Any ideas what I'm doing wrong? Thanks!

推荐答案

一些问题不分先后.

首先，在行级触发器的主体中，需要使用:new和:old来引用新旧记录.前导冒号是必要的.所以你的 WHERE 子句需要是

First, in the body of a row-level trigger, you need to use :new and :old to reference the new and old records. The leading colon is necessary. So your WHERE clause would need to be

WHERE PROJECTID = :new.PROJECTID

第二，如果您在 SQL*Plus 中运行 CREATE TRIGGER，您可以使用 SHOW ERRORS 命令获取错误和警告列表，即>

Second, if you are running your CREATE TRIGGER in SQL*Plus, you can get a list of the errors and warnings using the SHOW ERRORS command, i.e.

SQL> show errors

您还可以查询 DBA_ERRORS 表(或 ALL_ERRORS 或 USER_ERRORS，具体取决于您的权限级别)，但这不是您通常需要的诉诸.

You could also query the DBA_ERRORS table (or ALL_ERRORS or USER_ERRORS depending on your privilege level) but that's not something you normally need to resort to.

第三，假设语法错误得到纠正，你将得到一个 如果您使用此逻辑，则会发生变异表错误.表 A 上的行级触发器(在本例中为 TPM_TRAININGPLAN)无法查询表 A，因为该表可能处于不一致的状态.您可以解决这个问题，正如 Tim 在他的文章中所示，通过创建一个包含集合的包，在 before 语句触发器中初始化该集合，在行级触发器中填充集合中的数据，然后在一个 after 语句触发器.然而，添加到系统中的复杂性相当高，因为您必须管理多个不同的对象.

Third, assuming the syntax errors get corrected, you're going to get a mutating table error if you use this logic. A row level trigger on table A (TPM_TRAININGPLAN in this case) cannot query table A because the table may be in an inconsistent state. You can work around that, as Tim shows in his article, by creating a package with a collection, initializing that collection in a before statement trigger, populating the data in the collection in a row-level trigger, and then processing the modified rows in an after statement trigger. That's a decent amount of complexity to add to the system, however, since you'll have to manage multiple different objects.

通常，最好将此逻辑作为用于操作 TPM_TRAININGPLAN 表的任何 API 的一部分来实现.如果这是一个存储过程，将更新 TPM_PROJECT 的逻辑放在该存储过程中而不是将其放在触发器中更有意义.尝试调试在触发器中嵌入了大量逻辑的应用程序是非常痛苦的，因为这使得开发人员很难准确地跟踪正在执行的操作.或者，您可以从 TPM_PROJECT 表中删除 TRAININGDELIVERYSTART 列，并在运行时计算最短开始日期.

Generally, you'd be better off implementing this logic as part of whatever API you use to manipulate the TPM_TRAININGPLAN table. If that is a stored procedure, it makes much more sense to put the logic to update TPM_PROJECT in that stored procedure rather than putting it in a trigger. It is notoriously painful to try to debug an application that has a lot of logic embedded in triggers because that makes it very difficult for developers to follow exactly what operations are being performed. Alternately, you could remove the TRAININGDELIVERYSTART column from TPM_PROJECT table and just compute the minimum start date at runtime.

第四，如果您的触发器在插入、更新和删除时触发，您不能简单地引用 :new 值.:new 对插入和更新有效，但如果您要删除，它将为 NULL.:old 对删除和更新有效，但如果您要进行插入，则将为 NULL.这意味着您可能需要遵循(参考 Tim 的包解决方案)

Fourth, if your trigger fires on inserts, updates, and deletes, you can't simply reference :new values. :new is valid for inserts and updates but it is going to be NULL if you're doing a delete. :old is valid for deletes and updates but is going to be NULL if you're doing an insert. That means that you probably need to have logic along the lines of (referencing Tim's package solution)

BEGIN
  IF inserting 
  THEN
    trigger_api.tab1_row_change(p_id => :new.projectid, p_action => 'INSERT');
  ELSIF updating
  THEN
    trigger_api.tab1_row_change(p_id => :new.projectid, p_action => 'UPDATE');
  ELSIF deleting
  THEN
    trigger_api.tab1_row_change(p_id => :old.projectid, p_action => 'DELETE');
  END IF;
END;

相关文章