About those auditing columns… a change in perspective.

I’m a bit of a database pureist. I believe that if you are building a system that will capture information for a business or organization, and that information matters to the business, then you should build the tables that will support the insert, update, and delete of information in third normal form (at least, there are additional normal forms) and the best way to figure out how those tables should be built is to work directly with the business by building an Entity Relationship Diagram (note: a Table Diagram is NOT an ERD!) and getting business by-in before you start creating tables.

A frequent business requirement is “We’d like to know by who and when information was created or updated.” Traditionally, we have added four columns to our tables to do this: CREATED, CREATED_BY, UPDATED, and UPDATED_BY. As an aside (I have a feeling there will be a lot of them in this post), I despise when folks do CREATED_DATE, and UPDATED_DATE. You don’t do NAME_VARCHAR2!

Oracle Quick SQL, built into Oracle APEX, builds these same traditional columns on your tables if you ask it to, and then they build a BUI (before update or insert) trigger on the table. Note, I think that a BUI trigger is “wrong” since we’ve had compound triggers in the database since Oracle 11g, and that every table that has normal triggers (basically, if you are not using Edition Based Redefinition) should only have a single trigger, and it should be a compound trigger.

That said, quick SQL builds a trigger that looks like this:

create or replace trigger employee_biu
    before insert or update
    on employee
    for each row
begin
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end employee_biu;
/

As a purist, I have always hated that the trigger always populates the UPDATED and UPDATED_BY columns even on the first insert. To me, those columns should only be populated if the row was actually updated, since that is the name of the columns. By populating them all the time, if you want to answer the question “How many rows have actually been updated?”, you will need to compare (let’s use a table with a hundred million rows) a hundred million values:

select count(*)
  from sometable
 where created != updated;

In addition, you are going to store extra stuff that you don’t really need. Instead, I believed (notice the past tense!), those columns should be nullable and should only be populated if you actually update the column. A hundred million row table with only seven updated rows and an index on updated would be worlds faster than a table that stores all these needless UPDATED and UPDATED_BY by values.

Now, because we have a nullable column, we do introduce the “issues” that come along with a nullable column. You’ll need to use NVL or, even better, COALESCE every time you look at the column, and if you forget, you can end up with bugs. The number of bugs that nulls give us in systems is pretty large. But, because you did things “right” (you didn’t needlessly populate a column that didn’t need to be populated), you would be aware of this, and you wouldn’t fall prey to those bugs.

However, a recent conversation with Anton Nielsen convinced me to update my perspective.

At first, Anton tried to make the argument that every insert is an update too. I wasn’t buying that argument at all. He, of course, brought up all the issues with a nullable column and indexes on virtual columns (Why not add a virtual column that is a COALESCE(UPDATED,CREATED) as LAST_TOUCHED?), etc. But the thing that really convinced me was this:

“In all my years of building applications the business people always ask ‘Who touched this row last?’, they never ask ‘How many rows were touched after they were created?’.”

Anton Nielsen

Maybe those columns should have been called LAST_TOUCHED and LAST_TOUCHED_BY (and, indeed, if you are going to populate them on insert, they really should be), but we can fix this by adding a comment to the UPDATED and UPDATED_BY columns:

comment on column employee.updated is 'Populated during row creation and whenever the row is updated. Effectively that makes this column the LAST_TOUCHED column.';

comment on column employee.updated_by is 'Populated during row creation and whenever the row is updated. Effectively that makes this column the LAST_TOUCHED_BY column. This is set to the current APEX user or the database user depending on the context.';

Let me know what you think.