PostgreSQL使用触发器来更新表

I wanted to clear the latitude when I modify the address of table CarBay by using a trigger here. But it deletes all latitudes in this table. What can I do to fix this problem?

当我在这里使用触发器修改表CarBay的地址时，我想清除纬度。但它会删除此表中的所有纬度。我该怎么做才能解决这个问题？

CREATE TABLE CarBay(
       carBayName   VARCHAR(20),
       address      VARCHAR(50)     NOT NULL,
       description  VARCHAR(50),  
       latitude     DECIMAL(8,5),
       longitude    DECIMAL(8,5),
       PRIMARY KEY (carBayName)
);

    CREATE FUNCTION changeBayName() RETURNS trigger AS $$
        BEGIN 
            UPDATE CarBay
            SET latitude = NULL
            WHERE OLD.address != NEW.address;
            RETURN NEW;
        END
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER changeBay AFTER UPDATE OF address ON CarBay 
                        FOR EACH ROW 
                        EXECUTE PROCEDURE changeBayName();

1 个解决方案

#1

When you filter OLD.address != NEW.address you change all addresses because by definition the old address is updated to a new address (the AFTER UPDATE OF address clause in the trigger). Instead, you should only clear the latitude (and presumably the longitude) where the address in the table is equal to the NEW address.

当您过滤OLD.address！= NEW.address时，您将更改所有地址，因为根据定义，旧地址将更新为新地址（触发器中的AFTER UPDATE OF地址子句）。相反，您应该只清除表中地址等于NEW地址的纬度（可能是经度）。

CREATE FUNCTION changeBayName() RETURNS trigger AS $$
BEGIN 
    UPDATE CarBay
    SET latitude = NULL, longitude = NULL
    WHERE address = NEW.address;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;