如何触发更新另一个表中的列？ [英] How do I make a trigger to update a column in another table?

问题描述

因此，我正在为应用服务器的数据库添加上次更新时间。想法是，它将记录对我们的行程之一应用更新的时间，然后该应用可以发送获取请求，以了解是否已获取所有正确的最新信息。

So I am working on adding a last updated time to the database for my app's server. The idea is that it will record the time an update is applied to one of our trips and then the app can send a get request to figure out if it's got all of the correct up to date information.

我已将列添加到我们的表中，并提供了全部服务，最终设法获得一个触发器，以便每次在行程表中对行程进行更改时都会更新该列。我的问题现在来自以下事实：与旅行有关的信息也存储在许多其他表中（例如，存在构成旅行的路线的表以及用户可以在行程等），并且如果其中任何数据发生更改，则行程的更新时间也需要更改。我一辈子都无法弄清楚如何设置触发器，以便在我更改某些路线信息时，会在该表中更新该路线所属行程的最后更新时间。

I've added the column to our table, and provided the service for it all, and finally manage to get a trigger going to update the column every time a change is made to a trip in it's trip table. My problem now comes from the fact that the information that pertains to a trip is stored across a multitude of other tables as well (for instance, there are tables for the routes that make up a trip and the photos that a user can see on the trip, etc...) and if any of that data changes, then the trip's update time also needs to change. I can't for the life of me figure out how to set up the trigger so that when I change some route information, the last updated time for the trip(s) the route belongs to will be updated in it's table.

这是我现在的触发代码：当该行的行被更新时，它将更新该行表的最后更新的列。

This is my trigger code as it stands now: it updates the trip table's last updated column when that trip's row is updated.

CREATE OR REPLACE FUNCTION record_update_time() RETURNS TRIGGER AS
    $$
        BEGIN
            NEW.last_updated=now();
            RETURN NEW;
        END;
    $$ 
    LANGUAGE PLPGSQL;

CREATE TRIGGER update_entry_on_entry_change
    BEFORE UPDATE ON mydatabase.trip FOR EACH ROW
    EXECUTE PROCEDURE record_update_time();

--I used the next two queries just to test that the trigger works. It
--probably doesn't make a difference to you but I'll keep it here for reference
UPDATE mydatabase.trip
    SET title='Sample New Title'
    WHERE id = 2;
SELECT *
    FROM mydatabase.trip
    WHERE mydatabase.trip.id < 5;

现在，当引用带有外键的行的行更新时，我需要对其进行更新。

Now I need it to update when the rows referencing the trip row with a foreign key get updated. Any ideas from someone more experienced with SQL triggers than I?

推荐答案

mydatabase 是 schema 的一个非常不幸的名字。

mydatabase is a remarkably unfortunate name for a schema.

触发函数可能看起来像这样：

The trigger function could look like this:

CREATE OR REPLACE FUNCTION trg_upaft_upd_trip()
  RETURNS TRIGGER AS
$func$
BEGIN

UPDATE mydatabase.trip t    -- "mydatabase" = schema name (?!)
SET    last_updated = now()
WHERE  t.id = NEW.trip_id   -- guessing column names      

RETURN NULL;                -- calling this AFTER UPDATE

END
$func$  LANGUAGE plpgsql;

并且需要在每个相关表的触发器中使用（不适用于行程本身）：

And needs to be used in a trigger on every related table (not on trip itself):

CREATE TRIGGER upaft_upd_trip
AFTER UPDATE ON mydatabase.trip_detail
FOR EACH ROW EXECUTE PROCEDURE trg_upaft_upd_trip();

您还需要支付 INSERT 和 Delete （可能还有 COPY ）在所有子表上...

You also need to cover INSERT and DELETE (and possibly COPY) on all sub-tables ...

此方法有许多潜在的失败点。或者，考虑一个查询或视图，该查询或视图动态地从子表中计算最新的 last_updated 。如果您经常更新，这可能是更好的方法。

如果您很少 UPDATE 和 SELECT 通常，您的第一种方法可能会付钱。

This approach has many potential points of failure. As alternative, consider a query or view that computes the latest last_updated from sub-tables dynamically. If you update often this might be the superior approach.
If you rarely UPDATE and SELECT often, your first approach might pay.

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