oracle PL/SQL 触发器从一个表上的 INSERT 更新另一个表

PL/SQL Trigger to update another table from INSERT on one table

提问by forcey123

I'm using SQL and an Oracle database and need some help - triggers are something I struggle to understand.

我正在使用 SQL 和 Oracle 数据库，需要一些帮助 - 触发器是我很难理解的东西。

I need a trigger for when I insert a row into Table A so that it updates a row on Table B: specifically the row whose primary key matches the corresponding foreign key of the row that just been added to Table A.

当我在表 A 中插入一行时，我需要一个触发器，以便它更新表 B 上的一行：特别是主键与刚刚添加到表 A 的行的相应外键匹配的行。

So for example column X in Table A is a foreign key that references column Y in Table B (the primary key). When I add a row to Table A I need column Z of Table B to have 1 added to its numeric value in the row where column X = column Y.

因此，例如表 A 中的列 X 是引用表 B 中的列 Y（主键）的外键。当我向表 AI 添加一行时，需要表 B 的 Z 列在 X 列 = Y 列的行中为其数值添加 1。

This is what I have been able to get so far in SQL based on my limited understanding of triggers, in case it helps (I realise it's not very good, treat it as pseudocode):

基于我对触发器的有限理解，这是我迄今为止在 SQL 中能够得到的，以防万一（我意识到它不是很好，将其视为伪代码）：

CREATE OR REPLACE TRIGGER test_trig
AFTER INSERT OR UPDATE ON tableA
FOR EACH ROW

BEGIN
  UPDATE tableB
  SET columnZ = columnZ + 1
  WHERE tableA.columnX = tableB.columnY;
END test_trig;
/

Thanks

谢谢

回答by user2001117

try this :

尝试这个 ：

Syntax will be

语法将是

CREATE OR REPLACE TRIGGER test_trig
AFTER INSERT OR UPDATE ON tableA
FOR EACH ROW

BEGIN
  UPDATE tableB
  SET columnZ = columnZ + 1
  WHERE tableB.columnX = :NEW.columnX;
END test_trig; 
/

:new.columnX reference the table A columnX.

:new.columnX 引用表 A columnX。

回答by Mt. Schneiders

If tableB.columnZ represents the count of the referenced tableA records, there's no meaning triggering on tableA's UPDATE unless tableA's reference column can change.

如果 tableB.columnZ 表示引用的 tableA 记录的计数，则在 tableA 的 UPDATE 上触发没有意义，除非 tableA 的引用列可以更改。

First Ocasion: tableA.ReferenceColumn does not change:

第一次：tableA.ReferenceColumn 不会改变：

CREATE OR REPLACE TRIGGER test_trig
AFTER INSERT ON tableA
FOR EACH ROW
BEGIN

  UPDATE tableB
  SET columnZ = columnZ + 1
  WHERE tableB.columnX = :NEW.columnX;
END test_trig;
/

Second Ocasion: tableA.ReferenceColumn does change:

第二个场合：tableA.ReferenceColumn 确实改变了：

CREATE OR REPLACE TRIGGER test_trig
AFTER INSERT OR UPDATE OF columnX ON tableA
FOR EACH ROW

BEGIN

  IF UPDATING AND nvl(:OLD.columnX,0) <> 0 THEN
      UPDATE tableB
      SET columnZ = columnZ - 1
      WHERE tableB.columnX = :OLD.columnX;
  END IF:

  IF nvl(:NEW.columnX,0) <> 0 THEN
      UPDATE tableB
      SET columnZ = columnZ + 1
      WHERE tableB.columnX = :NEW.columnX;  
  END IF;

END test_trig;
/

Third Ocasion: tablaA records can be deleted:

第三个场合：可以删除 tablaA 记录：

CREATE OR REPLACE TRIGGER test_trig
AFTER INSERT OR DELETE OR UPDATE OF columnX ON tableA
FOR EACH ROW

BEGIN
  IF (UPDATING OR DELETING) AND nvl(:OLD.columnX,0) <> 0 THEN
      UPDATE tableB
      SET columnZ = columnZ - 1
      WHERE tableB.columnX = :OLD.columnX;
  END IF:

  IF nvl(:NEW.columnX,0) <> 0 THEN
      UPDATE tableB
      SET columnZ = columnZ + 1
      WHERE tableB.columnX = :NEW.columnX;  
  END IF;
END test_trig;
/

回答by haki

I'm guessing you are implementing some mechanism for (A) keeping history (B) counter or (C) data integrity issue. if this is the case i would suggest performing the update using a pl/sql package that would handle all necessary updates/other DML operations. it's a best practice for the application to update data via pl/sql packages. this way you can control the process internally and it's much more easy to maintain. plus, you can save your self problems in the future when after you forget you have a trigger on that table.

我猜你正在实施某种机制来 (A) 保持历史 (B) 计数器或 (C) 数据完整性问题。如果是这种情况，我建议使用 pl/sql 包执行更新，该包将处理所有必要的更新/其他 DML 操作。应用程序通过 pl/sql 包更新数据是最佳实践。通过这种方式，您可以在内部控制流程，并且更易于维护。此外，当您忘记该表上有触发器时，您可以在将来避免自己的问题。

one tip i can provide you about triggers - before you decide to use triggers make sure you've exhausted every other possibility.

我可以为您提供有关触发器的一个提示 -在您决定使用触发器之前，请确保您已经用尽了所有其他可能性。

回答by Randy

good enough start.

足够好的开始。

first - let's get this out of the way - this is not well normalized - the value you describe seems like it should be calculated at runtime not at data manipulation time.

首先 - 让我们解决这个问题 - 这没有很好地标准化 - 您描述的值似乎应该在运行时而不是在数据操作时计算。

consider the following:

考虑以下：

insert = +1 to the column - ok

插入 = +1 到该列 - 好的

update = ? not always +1 to the column i suppose - probably only when certain other data is modified. for example - what if i update table a set col1=col1. maybe what you want maybe not.

更新 = ? 我想并不总是对列+1 - 可能只有在修改某些其他数据时。例如 - 如果我更新表集合 col1=col1。也许你想要的也许不是。

delete = ? does delete mean -1 to the column?

删除 = ? 删除是否意味着 -1 到列？

for syntax:

对于语法：

WHERE tableA.columnX = tableB.columnY;

should be

应该

WHERE :new.columnX = tableB.columnY;