PostgreSQL – How to trigger columns in another table?

CREATE OR REPLACE FUNCTION record_update_time() RETURNS TRIGGER AS
 $$
 BEGIN
 NEW.last_updated=now();
 RETURN NEW;
 END;< br /> $$
 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 Ti tle'
 WHERE id = 2;
SELECT *
 FROM mydatabase.trip
 WHERE mydatabase.trip.id <5;

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 < br />
RETURN NULL; - calling this AFTER UPDATE

END
$func$ LANGUAGE plpgsql;

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

So I am adding the last updated database time for my application server. Our idea is that it will apply the record update to the time of one of our trips, and then the application can send Get the request to determine if it has all the correct and up-to-date information.

I have added the column to our table and provided it with all the There is a service, and finally managed to get a trigger to update the column every time a change is made to the itinerary in its itinerary table. My problem now comes from the fact that travel-related information is also stored in many other tables (for example, there are The itinerary that composes the trip and the photos that the user can see. Trips, etc...) If any data changes, then the update time of the itinerary also needs to be changed. I can’t figure out for the life of me how to set the trigger so that when I change some When routing information, the last update time of the itinerary to which the route belongs will be updated in its table.

This is my current trigger code: when the row of the itinerary is updated, it will update the last updated column of the itinerary table.

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< br /> WHERE mydatabase.trip.id <5;

Now I need it to update the row update of the row referencing the row with the foreign key. From the more experienced S QL trigger people think better than mine?

Mydatabase is a very unfortunate schema name.

The trigger function may look like this:

< p>

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 need to be used in the trigger on each related table (not on the trip itself ):

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

You You also need to overwrite INSERT and DELETE (and possibly COPY) on all child tables...

This method has many potential failure points. Or, consider a query that dynamically calculates the latest last_updated from the child table or View. If you update frequently, this may be a better method. If you rarely update and choose frequently, your first method may pay a price.