w3resource

Automatically Archive Deleted Records with a Trigger


Archival Trigger using Stored Procedure

Write a PostgreSQL query to create a trigger that calls a stored procedure to archive records into a history table when they are deleted from the main table.

Solution:

-- Create or replace a function named archive_record
CREATE OR REPLACE FUNCTION archive_record() RETURNS TRIGGER AS $$
-- Begin the function block
BEGIN
    -- Insert the old record into archive_table
    INSERT INTO archive_table SELECT OLD.*;
    -- Return the old record after archiving
    RETURN OLD;
-- End the function block
END;
$$ LANGUAGE plpgsql;

-- Create a trigger named trg_archive_record
CREATE TRIGGER trg_archive_record
-- Set the trigger to execute after a row is deleted from main_table
AFTER DELETE ON main_table
-- Apply the trigger to each row deleted
FOR EACH ROW EXECUTE FUNCTION archive_record();

Explanation:

  • Purpose of the Query:
    • The goal is to preserve deleted records by archiving them into a separate history table.
    • This demonstrates using a stored procedure within a trigger to maintain historical data.
  • Key Components:
    • The stored procedure uses the OLD record to capture the state before deletion.
    • The trigger is set to fire after a DELETE operation.
  • Real-World Application:
    • Useful for compliance, auditing, or recovery scenarios where historical data is important.

Notes:

  • The archive_table should have a structure matching main_table.
  • Consider storage and performance implications of archiving data.

For more Practice: Solve these Related Problems:

  • Write a PostgreSQL query to create a trigger that archives records into a history table when a deletion flag is set.
  • Write a PostgreSQL query to create a trigger that archives both updated and deleted records into a versioned history table.
  • Write a PostgreSQL query to create a trigger that moves records to an archive table and compresses the archived data.
  • Write a PostgreSQL query to create a trigger that periodically archives records based on a scheduled condition from a timestamp column.


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

Previous PostgreSQL Exercise: Data Validation Trigger using Stored Procedure.

Next PostgreSQL Exercise: Notification 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.