ALTER TABLE and the Audit Trail

Index to all the "Audit Trail" posts February 23, 2008  Tip: Triggering an Audit Trail February 28, 2009  Revisited: Triggering an Audit Trail March 24, 2009  ALTER TABLE and the Audit Trail March 18, 2013  Reporting and the Audit Trail

Possibly the most common schema change made to an existing table is to add a column. For example, here's an ALTER TABLE to add a column called display_order:

ALTER TABLE category ADD display_order BIGINT NOT NULL DEFAULT 0;

Here's the original table:

CREATE TABLE DBA.category ( -- 6 rows, 56k total = 8k table + 0 ext + 48k index
   category           /* PK        */ VARCHAR ( 50 ) NOT NULL,
   abbreviation       /*       U   */ VARCHAR ( 10 ) NOT NULL,
   display_category   /*       U   */ VARCHAR ( 100 ) NOT NULL,
   CONSTRAINT ASA81 PRIMARY KEY ( -- 16k
      category ),
   CONSTRAINT ASA82 UNIQUE ( -- 16k
      abbreviation ),
   CONSTRAINT ASA83 UNIQUE ( -- 16k
      display_category )
 );

But what if all data changes made to the category table are already being recorded in the logged_category table, as described in Revisited: Triggering an Audit Trail?

Old rows in logged_category won't look like the rows in category any more, they will be missing the new column.

The solution is surprisingly simple: after running the ALTER TABLE on category, but before running any more INSERT, UPDATE or DELETE operations on that table, add the column to the logged_category table:

ALTER TABLE logged_category ADD display_order BIGINT;

In keeping with the design of the log tables all constraints are omitted from the logged columns. In this situation that means all existing rows in logged_category will have NULL values in the new display_order column... probably a good choice since that column did not exist when those rows were inserted.

No other changes are required. In particular, no changes to the audit trail trigger are required because

• INSERT WITH AUTO NAME automatically matches SELECT list items with the INSERT table column names by name, and

• SELECT * automatically generates a column name list containing all the columns in the category table.

You may have heard people shout "Never Use Select Star!"... that may be good advice when you're coding a SQL query in a language like Java or VB, but it's unnecessarily limiting when you're coding application logic in SQL itself.

Here is the full schema modification script including UPDATE statements to fill in the new column. Exactly one line of code (the ALTER TABLE logged_category) has been added to deal with the fact that an audit trail is being maintained:

ALTER TABLE category        ADD display_order BIGINT NOT NULL DEFAULT 0;
ALTER TABLE logged_category ADD display_order BIGINT;

UPDATE category SET display_order = 60 WHERE category = 'BOOKS';
UPDATE category SET display_order = 40 WHERE category = 'COLLECTIBLES';
UPDATE category SET display_order = 30 WHERE category = 'GIFTWARE';
UPDATE category SET display_order = 50 WHERE category = 'LINENS & TEXTILES';
UPDATE category SET display_order = 20 WHERE category = 'POTTERY & GLASS';
UPDATE category SET display_order = 10 WHERE category = 'SILVERWARE';
COMMIT;

SELECT * FROM logged_category ORDER BY log_id;

Here's the output of the SELECT showing the history of all changes to the category table; NULL values in display_order for old rows, non-NULL for the new rows:



For the record, here's what the tables look like after the change, plus the (unchanged) audit trail trigger:

CREATE TABLE DBA.category ( -- 6 rows, 56k total = 8k table + 0 ext + 48k index
   category           /* PK        */ VARCHAR ( 50 ) NOT NULL,
   abbreviation       /*       U   */ VARCHAR ( 10 ) NOT NULL,
   display_category   /*       U   */ VARCHAR ( 100 ) NOT NULL,
   display_order                      BIGINT NOT NULL DEFAULT 0,
   CONSTRAINT ASA81 PRIMARY KEY ( -- 16k
      category ),
   CONSTRAINT ASA82 UNIQUE ( -- 16k
      abbreviation ),
   CONSTRAINT ASA83 UNIQUE ( -- 16k
      display_category )
 );

CREATE TABLE DBA.logged_category ( -- 18 rows, 24k total = 8k table + 0 ext + 16k index
   log_id             /* PK        */ UNSIGNED BIGINT NOT NULL DEFAULT autoincrement,
   logged_action                      VARCHAR ( 50 ) NOT NULL 
                                         CONSTRAINT ASA84 CHECK ( logged_action in( 
      'after INSERT','before UPDATE','after UPDATE','before DELETE' )  ),
   logged_at                          TIMESTAMP NOT NULL DEFAULT timestamp,
   category                           VARCHAR ( 50 ) NULL,
   abbreviation                       VARCHAR ( 10 ) NULL,
   display_category                   VARCHAR ( 100 ) NULL,
   display_order                      BIGINT NULL,
   CONSTRAINT ASA85 PRIMARY KEY ( -- 16k
      log_id )
 );

FYI, to create a new audit trail trigger for a different base table, all that's required is to edit all occurrences of 'category':

ALTER TRIGGER "log_category" 
   AFTER INSERT, UPDATE, DELETE ON category
   REFERENCING OLD AS old_category 
               NEW AS new_category
   FOR EACH STATEMENT
BEGIN
   DECLARE @current_timestamp TIMESTAMP;

   CASE

      WHEN INSERTING THEN

         INSERT logged_category
         WITH AUTO NAME
         SELECT 'after INSERT' AS logged_action, 
                *
           FROM new_category;

      WHEN UPDATING THEN

         SET @current_timestamp = CURRENT TIMESTAMP;

         INSERT logged_category
         WITH AUTO NAME
         SELECT 'before UPDATE'    AS logged_action,
                @current_timestamp AS logged_at,
                *               
           FROM old_category;

         INSERT logged_category
         WITH AUTO NAME
         SELECT 'after UPDATE'     AS logged_action,
                @current_timestamp AS logged_at,
                *               
           FROM new_category;

      WHEN DELETING THEN

         INSERT logged_category
         WITH AUTO NAME
         SELECT 'before DELETE' AS logged_action, 
                *               
           FROM old_category;

   END CASE;

END

---