PostgreSQL 触发器和行更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9955145/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL Trigger and rows updated
提问by Noon
I am trying to update a table according to this trigger:
我正在尝试根据此触发器更新表：
CREATE TRIGGER alert
AFTER UPDATE ON cars
FOR EACH ROW
EXECUTE PROCEDURE update_cars();
Trigger Function :
触发功能：
CREATE FUNCTION update_cars()
RETURNS 'TRIGGER'
AS $BODY$
BEGIN
IF (TG_OP = 'UPDATE') THEN
UPDATE hello_cars SET status = new.status
WHERE OLD.ID = NEW.ID;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
The trigger works fine. When the carstable is updated, the hello_carstable is updated but the status column in each row is updated and contains same new status! It must be updated according to a car ID.
I think my problem is in condition: WHERE OLD.ID = NEW.ID;but I can't tell what's wrong.
触发器工作正常。当cars表被更新时，hello_cars表被更新但每行中的状态列被更新并且包含相同的新状态！它必须根据汽车 ID 进行更新。
我认为我的问题有问题：WHERE OLD.ID = NEW.ID;但我不知道出了什么问题。
Thanks in advance.
提前致谢。
采纳答案by A.H.
OLDand NEWare aliases to the rows which fired the trigger. So when you execute a statement like
OLD和NEW是触发触发器的行的别名。所以当你执行像这样的语句时
UPDATE cars SET status='xyz' WHERE cars.id = 42;
then the trigger function will execute
然后触发函数将执行
UPDATE hello_cars SET status='xyz' WHERE 42 = 42
The part 42=42is always true. So each row in hello_carsis updated.
这部分42=42永远是真的。因此，hello_cars更新中的每一行。
You really want something like
你真的想要这样的东西
[...]WHERE hello_cars.id = OLD.ID
or a little shorter
或短一点
[...]WHERE id = OLD.ID
But you also need to think about what happens, if the initial update changes cars.id. In this case OLD.IDis not equal NEW.ID. What should happen in the table hello_carsin this case? But that's another question.
但是您还需要考虑如果初始更新发生变化会发生什么cars.id。在这种情况下OLD.ID是不相等的NEW.ID。hello_cars在这种情况下，表中应该发生什么？但这是另一个问题。
回答by a_horse_with_no_name
OLD.IDand NEW.IDare referencing valuesin the updated row of the table carsand thus (unless you change the ID in cars) will always evaluate to true and therefor all rows in hello_cars are updated.
OLD.ID并且NEW.ID正在引用表的更新行中的值，cars因此（除非您更改 中的 ID cars）将始终评估为 true，因此 hello_cars 中的所有行都会更新。
I think you probably want:
我想你可能想要：
UPDATE hello_cars
SET status = new.status
WHERE id = new.id;
This assumes that there is a column idin the table hello_carsthat matches the idin cars.
这假设id表中hello_cars有与idin匹配的列cars。
