Which types of triggers should a database administrator (DBA) create to automate the archiving of records in a database?

• A. BEFORE INSERT and AFTER DELETE
• B. AFTER INSERT and BEFORE DELETE
• C. BEFORE UPDATE and AFTER DELETES
• D. AFTER UPDATE and BEFORE INSERT

Answer: (B)

To effectively automate the archiving of records in a database, the creation of triggers that respond to specific events is crucial. The AFTER INSERT and BEFORE DELETE triggers are particularly suitable for this task.

The AFTER INSERT trigger is executed after a new record is added to the database. This is beneficial for archiving because once a record is created, the DBA can implement logic to move or copy this newly created record to an archive table or system. This ensures that records are maintained in a historical context without cluttering the main operational database.

The BEFORE DELETE trigger is triggered immediately before a record is deleted. This provides an opportunity to archive the record just before it is removed from the primary table, effectively preserving the data. It allows the DBA to implement functionality to save that record into an archival storage solution, thus ensuring data retention policies are met.

Together, these two triggers (AFTER INSERT and BEFORE DELETE) cover both ends of the data lifecycle concerning archiving: they capture new data for retention and safeguard existing data from loss during deletion. This strategy effectively streamlines the archiving process and ensures compliance with data retention policies.