Oracle - 创建用于更新另一个表的触发器的问题 [英] Oracle - Problem creating trigger that updates another table

问题描述

我已经阅读了关于创建触发器的Oracle文档，并且正在做的事情完全如何显示，但这只是不工作。我的目标是更新TPM_PROJECT表，其中最小的STARTDATE出现在TPM_TRAININGPLAN表中。因此，每次有人更新TPM_TRAININGPLAN中的STARTDATE列时，我想更新TPM_PROJECT表。这是我的尝试：

  CREATE TRIGGER Trigger_UpdateTrainingDelivery 
删除或插入或更新开始日期
 ON TPM_TRAININGPLAN 
每个ROW WHEN（new.TRAININGPLANTYPE ='prescribed'）
 BEGIN 
 UPDATE TPM_PROJECT SET TRAININGDELIVERYSTART =（SELECT MIN（TP.STARTDATE）FROM TPM_TRAININGPLAN TP WHERE TP.PROJECTID = new .PROJECTID和TP.TRAININGPLANTYPE ='prescribed'）
 WHERE PROJECTID = new.PROJECTID 
 END; 
  

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

 警告：---> 
 W（1）：警告：执行完成，警告
 <--- 
  

当然，Oracle不够好，实际上告诉我这是什么警告，我只是显示有一个。

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

  UPDATE TPM_TRAININGPLAN 
 set STARTDATE = to_date（'03 / 12/2009'，'mm / dd / yyyy'）
其中TRAININGPLANID = 15916; 
  

我收到错误讯息：

 > [错误]脚本行：20-22 ------------------------ 
 ORA- 04098：trigger'TPMDBO.TRIGGER_UPDATETRAININGDELIVERY'无效，重新验证失败
脚本第20行，语句第1行第7列
  

任何想法我做错了什么？感谢！

解决方案

有几个问题没有特定的顺序。

首先，在行级触发器的主体中，需要使用：new 和：old 新旧记录。冒号是必要的。因此，您的 WHERE 子句将需要

  WHERE PROJECTID =：new .PROJECTID 
  

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

  SQL>显示错误
  

您也可以查询 DBA_ERRORS 表（或 ALL_ERRORS 或 USER_ERRORS ，具体取决于您的权限级别），但这不是通常需要使用的。 / p>

第三，假设语法错误得到纠正，您将得到 mutating table error 如果您使用此逻辑。表A上的行级触发器（在此情况下为 TPM_TRAININGPLAN ）无法查询表A，因为该表可能处于不一致的状态。你可以解决这个问题，正如Tim在他的文章中所示，通过创建一个包含集合的包，在before语句触发器中初始化该集合，在行级触发器中填充集合中的数据，然后处理修改的行一个after语句触发器。因为你必须管理多个不同的对象。

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

第四，如果触发器在插入，更新和删除时触发，则不能简单地引用：new 值。 ：new 对插入和更新有效，但如果您要删除，它将为NULL。 ：old 对删除和更新有效，但如果你正在做一个插入，它将为NULL。这意味着你可能需要有逻辑（引用Tim的包解决方案）

  BEGIN 
如果插入
 THEN 
 trigger_api.tab1_row_change（p_id =>：new.projectid，p_action =>'INSERT'）; 
 ELSIF更新
 THEN 
 trigger_api.tab1_row_change（p_id =>：new.projectid，p_action =>'UPDATE'）; 
 ELSIF删除
 THEN 
 trigger_api.tab1_row_change（p_id =>：old.projectid，p_action =>'DELETE'）; 
 END IF; 
 END; 
  

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

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;

I get the error message:

>[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!

解决方案

A few issues in no particular order.

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

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

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.

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.

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.

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;

这篇关于Oracle - 创建用于更新另一个表的触发器的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！