Generate a backup trigger for tables

Question:

I need to implement a "backup trigger" on all of the tables in my application, so that whenever anyone changes the data in one of the tables, the existing data is copied to the backup table. I really, really don't want to have to write these myself for 100 tables. What is the best (you know what I mean: easiest) way to do that?. —Andy

Answer:

When you are thinking about implementing automated backups of your data, you should first of all check out the options that Oracle offers to help you do this transparently - that is, without writing lots of code yourself.
I suggest, in particular, that you investigate fine-grained auditing, described by Oracle as follows:
"Fine-grained auditing allows the monitoring of data access based on content. It provides granular auditing of queries, as well as INSERT, UPDATE, and DELETE operations. For example, a central tax authority needs to track access to tax returns to guard against employee snooping, with enough detail to determine what data was accessed. It is not enough to know that SELECT privilege was used by a specific user on a particular table. Fine-grained auditing provides this deeper functionality."
Read more here.
You might also want to look into the flashback query option. Introducing in Oracle 9i Database Release 2, "Oracle Flashback Query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system change number (SCN)." For more information, check out: this article.
OK, having pointed out a built-in alternative or two, you may very well still be wanting to do this yourself with a database trigger. So let's take a look at how you can do this for lots of tables without spending hours and hours writing incredibly tedious code.
The answer to this challenge may be found in the following two aspects of Oracle technology:

• The Oracle data dictionary, specifically the USER_TAB_COLUMNS view

•Native dynamic SQL, in particular the EXECUTE IMMEDIATE statement

USER_TAB_COLUMNS

This view contains information about all of the columns in each of the tables that you own. You can write a query against this view to generate both the "create copy table" statement and the trigger to populate that table.
The following query, for example, retrieves the name and data type of each column in the employees table:
SELECT column_name, data_type
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY column_id
The column_id column in the view indicates the position of the column within the table. We can use this information to generate the trigger logic that will copy the "before" data to the backup table.

Native Dynamic SQL

The only way you can create a table or trigger from within a PL/SQL block is with a dynamic SQL statement, and native dynamic SQL, introduced in Oracle 8i, is the easiest way to do this.
The following block of code, for example, creates a table that is the spitting image (minus all data) of the employees table:
BEGIN
EXECUTE IMMEDIATE
'create table emp_copy as
select * from employees where 1 = 2';
END;
By the way, when you execute a DDL statement (data definition language, like create table), Oracle will also implicitly issue a commit in your session.