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.