For example, several places I have worked have kept application settings in a table in the database. And I've run into situations where a value was updated and all of the sudden things started to go wrong. It is those times I wished that we had some sort of archiving mechanism so we could easily tell what changed, when, and what the previous values were.
This is something that can be easily accomplished with a trigger. And the nice thing about the trigger solution is that this happens transparently in the background. It doesn't matter if a row is updated by application code or an ad-hoc query in a SQL Editor, the prior version of the row will get archived. So lets see how this works.
First, consider that we have the following data table.
CREATE TABLE students
(
student_id NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL,
first_name VARCHAR2(30) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
email VARCHAR2(50) NULL,
phone VARCHAR2(20) NULL,
create_date DATE NOT NULL,
modified_date DATE NOT NULL,
CONSTRAINT pk_students PRIMARY KEY (student_id)
);
Then, we have this as an archive table. Note, I am not enforcing a foreign key on the STUDENT_ID column back to the STUDENTS table from STUDENTS_ARCHIVE. If we deleted a row from the STUDENTS table, then there would be no parent key to reference, so this would not work.
CREATE TABLE students_archive
(
archive_id NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL,
student_id NUMBER(10) NOT NULL,
first_name VARCHAR2(30) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
email VARCHAR2(50) NULL,
phone VARCHAR2(20) NULL,
create_date DATE NOT NULL,
active_start_time DATE NOT NULL,
active_end_time DATE NOT NULL,
operation_type VARCHAR2(1) NOT NULL,
CONSTRAINT pk_students_archive PRIMARY KEY (archive_id)
);
And now for the trigger. Its quite simple really.
CREATE OR REPLACE TRIGGER tr_students_archive_row
AFTER UPDATE OR DELETE
ON students
FOR EACH ROW
DECLARE
op_type VARCHAR2(1);
BEGIN
CASE
WHEN UPDATING THEN
op_type := 'U';
WHEN DELETING THEN
op_type := 'D';
END CASE;
INSERT INTO students_archive (student_id, first_name, last_name,
email, phone, create_date,
active_start_time, active_end_time, operation_type)
VALUES (:old.student_id, :old.first_name, :old.last_name,
:old.email, :old.phone, :old.create_date,
:old.modified_date, sysdate, op_type);
END;
The trigger fires AFTER EACH ROW on an UPDATE or DELETE statement. I fire AFTER EACH ROW because of course I want to archive every row affected and also because I want to make sure the UPDATE or DELETE was successful.
Then, I use a CASE...WHEN statement to put a code into a variable indicating if an UPDATE or DELETE operation is being performed.
Finally, I insert the OLD data (via the :OLD) pseudorecord into the archive table. The time this version the record started being active is the modified_date on the old version of the record, and the time it stopped being active is right now. In this case, I am using DATE values, but you just as well could use TIMESTAMP values for more accuracy.
And that is it. Now, whenever a record is changed or deleted in our data table, we'll have a history of it in our archive table. It is a very clean solution to the problem.
If the updates and deletes against your table are mostly of the single row variety, then this trigger will work great for you. If you are doing a lot of bulk updates against the data in your table, then a compound trigger will likely offer better performance, which I'll cover in a future blog post.
