触发更新另一个表 [英] Trigger update another table

问题描述

我一直在尝试编写一个触发函数，以在更改父表一段时间后更新子表中的行. 我已经阅读了触发器过程文档，但是还没有真正掌握如何构建函数.

I have been trying to write a trigger function that updates the rows in the child table when the parent is changed for a while now. I have read Trigger procedure documentation but i have not really grasped how to build the functions.

这是我尝试过的不起作用...

This is what I have tried that does not work...

CREATE FUNCTION myschema.update_child() RETURNS trigger AS
$BODY$
BEGIN
UPDATE myschema.child 
set new.number = parent.number 
FROM myschema.parent
WHERE id = "id";
RETURN NEW;
END
$BODY$
LANGUAGE plpgsql

然后触发

CREATE TRIGGER update_child_after_update
   AFTER INSERT OR UPDATE OR DELETE
   ON myschema.child
   FOR EACH ROW
   EXECUTE PROCEDURE myschema.update_child();

有人能给些提示吗?
最好的问候

Does anyone have some tips to give?
Best regards

推荐答案

您无需在触发函数的主体中使用parent表，因为父表中的值在该函数中可用特殊变量OLD和NEW.在这种情况下，您只需要NEW.

You don't need to use the parent table in the body of the trigger function, because the values from the parent table are available in the function in the special variable OLD and NEW. In this case you only need NEW.

如果仅在更新时需要触发器，则可以定义仅更新触发器:

If you need the trigger only on update, than define a update-only trigger:

CREATE or replace FUNCTION update_child() RETURNS trigger AS
  $BODY$
BEGIN
  UPDATE child
  set number = NEW.number
  WHERE id = NEW.id;
  RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_child_after_update
AFTER UPDATE 
ON parent
FOR EACH ROW
EXECUTE PROCEDURE update_child(); 

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