Revisited: Triggering an 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

I'm really glad Jeff Atwood's given me permission to reinvent the wheel because the code in Tip: Triggering an Audit Trail needed work.



It's pretty much a rewrite, not a refactoring:

• the audit log of table t is now called logged_t instead of t_log so all the logged_* tables appear separately in an alphabetic list instead of mixed in with the base tables.

• The log_id column is now UNSIGNED because you never know when you might need another bazillion rows :)

• The values allowed in logged_action are now defined in a CHECK constraint. Feel free to drop that constraint, it's more for documentation than anything else since the trigger is the only piece of code that fills in a value... on the other hand, there's no safety like safety.

• The values in logged_action have been expanded for clarity, and a new one added: 'before UPDATE'.

• The before- and after-images of updated rows are stored instead of just the after-images. In this case the value of logged_at is set explicitly so both before- and after-images have exactly the same value.

• The logged_action column is defined as VARCHAR ( 50 ) instead of the minimum required VARCHAR ( 13 ) so folks don't have accidents when translating the code to different languages. There's no difference in storage efficiency or performance between the two definitions; that depends on the amount of data stored, not the declaration of maximum length.

• The logged_description and logged_by columns have been dropped because this application doesn't need them. If you do, see the original Tip: Triggering an Audit Trail.

• The three audit-related columns are all located at the front of the logged_t row, for efficiency. This no longer gives Foxhound a reason to produce "long columns (max length 100 or more) not all at end of row" messages in the Table-level Curiosities section. If the base table t does have all its long columns at the end of the row, they are also at the end of row in logged_t.

Here's the revised code, presented as an end-to-end example from the real world:

CREATE TABLE exchange_rate (
   from_currency_to_currency     VARCHAR ( 50 ) NOT NULL PRIMARY KEY,
   exchange_rate                 DECIMAL ( 15, 5 ) NOT NULL,
   updated_at                    TIMESTAMP NOT NULL DEFAULT TIMESTAMP );

CREATE TABLE logged_exchange_rate (
   log_id         UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   logged_action  VARCHAR ( 50 ) NOT NULL CHECK ( logged_action IN ( 
                     'after INSERT', 'before UPDATE', 'after UPDATE', 'before DELETE' ) ),
   logged_at      TIMESTAMP NOT NULL DEFAULT TIMESTAMP, 
   -- Columns from table to be logged...
   from_currency_to_currency     VARCHAR ( 50 ),
   exchange_rate                 DECIMAL ( 15, 5 ),
   updated_at                    TIMESTAMP );

CREATE TRIGGER log_exchange_rate
   AFTER INSERT, UPDATE, DELETE ON exchange_rate
   REFERENCING OLD AS old_exchange_rate 
               NEW AS new_exchange_rate
   FOR EACH STATEMENT
BEGIN
   DECLARE @current_timestamp TIMESTAMP;

   CASE

      WHEN INSERTING THEN

         INSERT logged_exchange_rate
         WITH AUTO NAME
         SELECT 'after INSERT' AS logged_action, 
                *
           FROM new_exchange_rate;

      WHEN UPDATING THEN

         SET @current_timestamp = CURRENT TIMESTAMP;

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

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

      WHEN DELETING THEN

         INSERT logged_exchange_rate
         WITH AUTO NAME
         SELECT 'before DELETE' AS logged_action, 
                *               
           FROM old_exchange_rate;

   END CASE;

END;

INSERT exchange_rate VALUES ( 'USD/CAD', 1.25210, DEFAULT );

UPDATE exchange_rate SET exchange_rate = 1.25211;

DELETE exchange_rate;

INSERT exchange_rate VALUES ( 'USD/CAD', 1.25212, DEFAULT );

COMMIT;

SET TEMPORARY OPTION TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:NN:SS.SSSSSS';

BEGIN
SELECT * FROM exchange_rate;
SELECT * FROM logged_exchange_rate ORDER BY log_id;
END;