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