w3resource

Propagate Parent Key Changes via Cascade Trigger


Cascade Update Trigger using Stored Procedure

Write a PostgreSQL query to create a trigger that calls a stored procedure to cascade update a foreign key field in a child table when a parent table's primary key is changed.

Solution:

-- Create or replace a function named cascade_update that returns a trigger
CREATE OR REPLACE FUNCTION cascade_update() RETURNS TRIGGER AS $$
BEGIN
    -- Update the parent_id in child_table to match the new ID in the parent table
    UPDATE child_table
    SET parent_id = NEW.id
    WHERE parent_id = OLD.id;
    -- Return the updated row
    RETURN NEW;
END;
-- Specify the language used in the function as PL/pgSQL
$$ LANGUAGE plpgsql;
-- Create a trigger named trg_cascade_update on parent_table to cascade update child_table on primary key change
CREATE TRIGGER trg_cascade_update
-- Trigger after an update of the id column in parent_table
AFTER UPDATE OF id ON parent_table
-- Execute the cascade_update function for each affected row
FOR EACH ROW EXECUTE FUNCTION cascade_update();

Explanation:

  • Purpose of the Query:
    • The goal is to maintain referential integrity by updating related foreign key values in a child table when the parent table's primary key changes.
    • This demonstrates using a stored procedure to perform a cascade update via a trigger.
  • Key Components:
    • The trigger listens for changes to the id column in the parent_table.
    • The stored procedure updates the corresponding records in the child_table.
  • Real-World Application:
    • Useful in systems where changes in a master record must be propagated to dependent records automatically.

Notes:

  • Verify that proper foreign key relationships exist between the tables.
  • Ensure that recursive triggers are prevented if necessary.

For more Practice: Solve these Related Problems:

  • Write a PostgreSQL query to create a trigger that cascades primary key changes to multiple child tables with composite keys.
  • Write a PostgreSQL query to create a trigger that cascades updates only if the new primary key satisfies a specific validation rule.
  • Write a PostgreSQL query to create a trigger that performs a cascade update and logs each update into a separate log table.
  • Write a PostgreSQL query to create a trigger that conditionally cascades updates based on a check against a status flag.


Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Timestamp Update Trigger using Stored Procedure.

Next PostgreSQL Exercise: Recursive Trigger Prevention using Stored Procedure.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.