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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics