w3resource

Sync Data Between Tables Using a Trigger Procedure


Data Synchronization Trigger using Stored Procedure

Write a PostgreSQL query to create a trigger that calls a stored procedure to synchronize data between two related tables upon update.

Solution:

-- Create or replace a function named sync_data
CREATE OR REPLACE FUNCTION sync_data() RETURNS TRIGGER AS $$
BEGIN
    -- Update the secondary_table to synchronize data with primary_table
    UPDATE secondary_table
    SET column_value = NEW.column_value
    WHERE secondary_table.ref_id = NEW.id;
    
    -- Return the new row after the update
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger named trg_sync_data
CREATE TRIGGER trg_sync_data
-- Set the trigger to execute after updating a row in the primary_table
AFTER UPDATE ON primary_table
-- Apply the trigger to each updated row
FOR EACH ROW EXECUTE FUNCTION sync_data();

Explanation:

  • Purpose of the Query:
    • The goal is to keep data in a secondary table synchronized with updates made in a primary table.
    • This demonstrates using a stored procedure to propagate changes between tables.
  • Key Components:
    • The stored procedure performs an UPDATE on the secondary_table based on the NEW values from primary_table.
    • The trigger is defined to fire after an update on the primary_table.
  • Real-World Application:
    • Useful in scenarios where data redundancy exists and synchronization is necessary.

Notes:

  • Ensure that the secondary_table has a foreign key reference to the primary_table.
  • Trigger execution should be optimized for performance.

For more Practice: Solve these Related Problems:

  • Write a PostgreSQL query to create a trigger that synchronizes new inserts from a primary table to a backup table in real time.
  • Write a PostgreSQL query to create a trigger that updates corresponding rows in a secondary table with conflict resolution on duplicate keys.
  • Write a PostgreSQL query to create a trigger that keeps timestamp columns in two related tables in sync upon each update.
  • Write a PostgreSQL query to create a trigger that synchronizes data between tables only when a record passes a specific validation check.


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

Previous PostgreSQL Exercise: Conditional Trigger Execution using Stored Procedure.

Next PostgreSQL Exercise: Data Validation Trigger 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.