Oracle SQL从触发器中的联接中更新表的正确语法是什么？

Question

我有三张桌子：

报告员:代表一条有几个列的电线(por_cur，设备A，设备B，.，iqu_cur，检查)

cab_portees :表示线路上的特定设备(在一行中可以有多个这些设备的实例)、列(id、por_cur (来自创作者的外键)、.、iqu_cur)

indice_qualite :表示另外两个表中的一个表，该表被认为是重要的数据。列(table_name，attribut_name，.，iqu_cur，检查)

我们检查表外程序和cab_portees中的数据以确保其正确，当检查一行时，我们将portees.examine更新为'1‘。我想做一个触发器来自动更新表indice_qualite :将表中的所有设备放在检查= '1‘。

我很难找到正确的语法，以下是我所拥有的：

    create or replace trigger PORTEES_EXAMINE_TRIGGER
    AFTER UPDATE ON PORTEES
    FOR EACH ROW

    BEGIN

    if :new.EXAMINE != :old.EXAMINE then

   UPDATE (SELECT INDICE_QUALITE.EXAMINE FROM 
          INDICE_QUALITE, PORTEES
          WHERE PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
          AND PORTEES.IQU_CUR = :old.IQU_CUR
          UNION
          SELECT INDICE_QUALITE.EXAMINE FROM 
          INDICE_QUALITE, CAB_PORTEES
          WHERE CAB_PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
          AND CAB_PORTEES.POR_CUR = :old.POR_CUR)

    SET INDICE_QUALITE.EXAMINE = :new.EXAMINE;

    end if;

    END PORTEES_EXAMINE_TRIGGER;

有什么想法吗？

Answer 1

不能用update查询编写联接。使用合并进行连接。

MERGE INTO INDICE_QUALITE M USING
  (SELECT EXAMINE,
   INDICE_QUALITE.IQU_CUR
   FROM INDICE_QUALITE,
        PORTEES
   WHERE PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
     AND PORTEES.IQU_CUR = :old.IQU_CUR
   UNION 
   SELECT EXAMINE,
   INDICE_QUALITE.IQU_CUR
   FROM INDICE_QUALITE,
        CAB_PORTEES
   WHERE CAB_PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
     AND CAB_PORTEES.POR_CUR = :old.POR_CUR) T ON (M.IQU_CUR = T.IQU_CUR) WHEN MATCHED THEN
UPDATE
SET EXAMINE = :new.EXAMINE;

Answer 2

试试这个：

UPDATE (SELECT INDICE_QUALITE.EXAMINE  FROM 
      INDICE_QUALITE, PORTEES
      WHERE PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
      AND PORTEES.IQU_CUR = :old.IQU_CUR
      UNION ALL
      SELECT INDICE_QUALITE.EXAMINE FROM 
      INDICE_QUALITE, CAB_PORTEES
      WHERE CAB_PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
      AND CAB_PORTEES.POR_CUR = :old.POR_CUR) t
SET t.EXAMINE = :new.EXAMINE;

但是，我不知道这是否适用于UNION ALL (确定它不适用于UNION)。另外，您应该更喜欢ANSI连接语法，而不是旧的Oracle连接语法。

否则，试着：

UPDATE (SELECT INDICE_QUALITE.EXAMINE  
        FROM INDICE_QUALITE
           JOIN CAB_PORTEES ON CAB_PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
      WHERE PORTEES.IQU_CUR = :old.IQU_CUR) t
SET t.EXAMINE = :new.EXAMINE;

UPDATE (SELECT INDICE_QUALITE.EXAMINE  
        FROM INDICE_QUALITE
           JOIN PORTEES ON CAB_PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
      WHERE CAB_PORTEES.POR_CUR = :old.POR_CUR) t
SET t.EXAMINE = :new.EXAMINE;

注意，只有在CAB_PORTEES.IQU_CUR和INDICE_QUALITE.IQU_CUR上有唯一索引/约束时，这种更新才能工作。

Answer 3

您可以这样重写查询：

UPDATE INDICE_QUALITE iq
   SET iq.EXAMINE = :new.EXAMINE
 WHERE EXISTS (SELECT 1
                 FROM PORTEES p
                WHERE p.IQU_CUR = iq.IQU_CUR
                  AND p.IQU_CUR = :old.IQU_CUR
                UNION ALL
               SELECT 1
                 FROM CAB_PORTEES cp
                WHERE cp.IQU_CUR = iq.IQU_CUR
                  AND cp.POR_CUR = :old.POR_CUR);

我已经将所有规则放在update语句的exists条件中。我不确定这是否有效，因为需求还不清楚--您没有解释任何事情，您只是提供了不工作的查询。