postgres update parent if entity / foreign key updates

I have a table recipes and every recipe can have multiple entries.

CREATE TABLE IF NOT EXISTS "recipes" (
    "ident" SERIAL PRIMARY KEY,
    "identifier" TEXT NOT NULL UNIQUE,
    "modified_on" TIMESTAMP WITH TIME ZONE DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS "entries" (
    "ident" SERIAL PRIMARY KEY,
    "recipe_id" INTEGER NOT NULL,
    "name" TEXT
    FOREIGN KEY ("recipe_id") REFERENCES "recipes"("ident") ON DELETE CASCADE
);

Now when a recipe is updated, the following function is called and updates my timestamp:

CREATE OR REPLACE FUNCTION update_recipe_timestamp_proc()
RETURNS TRIGGER AS $$
BEGIN
    NEW."modified_on" = now();
    PERFORM pg_notify('notify_recipes_update', CAST(NEW.ident AS text));
    RETURN NEW;   
END;
$$ language 'plpgsql';

Now I also want my timestamp modified_on to get updated whenever an entry is updated.

The following trigger is not working.

DROP TRIGGER IF EXISTS update_recipes_timestamp_e ON entries;
CREATE TRIGGER update_recipes_timestamp_e BEFORE UPDATE ON entries FOR EACH ROW EXECUTE PROCEDURE update_recipe_timestamp_proc();

ERROR: Record "NEW" has no field "modified_on"

Solution

I stated this in the comments, but I'll place it here for completeness.

Your current issue is that you are trying to set the value of a field that does not exist. NEW in a trigger is the actual record that you are inserting or updating AND because that is occurring on the entries table. Based on that, you can see why they would not work and why you are getting that error.

The solution to your issue is to remove that line NEW."modified_on" = now(); and instead replace it with an UPDATE statement that will update the recipe table.

Your final trigger code should look like:

CREATE OR REPLACE FUNCTION update_recipe_timestamp_proc()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE recipe SET modified_on = now() WHERE ident = NOW.recipe_id;
    PERFORM pg_notify('notify_recipes_update', CAST(NEW.ident AS text));
    RETURN NEW;   
END;
$$ language 'plpgsql';

The record you are inserting/updating in entries has the field recipe_id which you use in the UPDATE statement to ensure you are updating the correct record in the recipe table.