Mysql – What are the best practices for triggers to maintain a revision number on records

auditbest practicesMySQLtrigger

I have an audit trail implemented on an application database that captures old value, new value, modification time, and user inserting it into another table. I'm now wanting to add a revision number to each table to make idempondency checks more efficient.

As it stands, the trigger that captures all the data is AFTER UPDATE. My understanding is that if I want to update the row I'm operating on, I will need to use a BEFORE trigger. ie:

CREATE TRIGGER `Table_A_Audit_Trigger` BEFORE UPDATE ON `Table_A`;
FOR EACH ROW BEGIN;
SET NEW.revision = OLD.revision + 1;

Would it be better to have two separate triggers for this, or to move the insert into the BEFORE trigger as well?

EDIT: The code for the AFTER UPDATE trigger (as well as the table DDL itself) is created by the application layer, then run on the database (generally when the model is created or updated in the software). It would not be difficult to add creation of this trigger at the same time.

As I understand it, the downside to combining into a BEFORE trigger would be inconsistency if the audit insert succeeds, but the actual update fails. Putting everything in an AFTER trigger would require the use of an additional UPDATE on the target table, which further compromises performance, and can cause cascading issues as well.

Best Answer

Along the lines of my response to your earlier question, surely this would be better dealt with in the stored procedure or insert/update query that is responsible for the change. That would be the logical place to increment a version column, not a trigger.

If a trigger is the only way you can be sure the revision count will be implemented, I'd suggest an INSTEAD OF trigger on any tables you need this. You can deal with the revision increment and auditing in one place then.

Related Solutions

Mysql – What are some good methods of structuring a MySQL audit trail to be both immutable, and perform well for usage

Are there other ways this has been dealt with?

If you use MySQL Replication, you could have a Master/Slave that has the following:

AUDIT_LOG as InnoDB on the Master
AUDIT_LOG as ARCHIVE on the Slave

This would provide fast writing of the AUDIT_LOG on the Master and makes the data available for reads almost instantly. The Slave would have the backup as an ARCHIVE with a small disk footprint.

DO NOT SEND ANY ALTER TABLE SQL AGAINST AUDIT_LOG ONCE REPLICATION IS SET UP. Otherwise, you lose the small disk footprint feature of the ARCHIVE Storage Engine.

Any drawbacks or trade-offs that are apparent?

You must configure InnoDB

with enough RAM to accommodate data and indexes that are frequently queried : What are the main differences between InnoDB and MyISAM?
with tablespaces for table separate from ibdata1 : What is the best way to reduce the size of ibdata in mysql?
to access multiple CPUs (if using MySQL 5.5) : Possible to make MySQL use more than one core?

Mysql – Keeping 2 tables in sync

If you have this scenario

table_a has an auto_increment field
table_b and table_c have table structure identical to table_a
You are running MySQL 5.1+

you can create a Stored Procedure and a MySQL event instead of a Trigger

For the sake of example, let's say table_a looks like this:

CREATE TABLE table_a
(
    id not null auto_increment,
    ....
    primary key (id),
);

Create a Stored Procedure on the Slave to Copy Data to table_b and table_c

DELIMITER $$ 
DROP PROCEDURE IF EXISTS `Load_New_Data` $$ 
CREATE PROCEDURE `Load_New_Data` () 
BEGIN
    DECLARE min_id,min_idb,mid_idc INT;

    SELECT MAX(id) INTO min_idb FROM table_b;
    SELECT MAX(id) INTO min_idc FROM table_c;
    SET min_id = IF(min_idb < min_idc,min_idb,min_idc);

    DROP TABLE IF EXISTS table_a_delta;
    CREATE TABLE table_a_delta SELECT * FROM table_a WHERE 1=2;
    INSERT INTO table_a_delta SELECT * FROM table_a WHERE id >= min_id;
    INSERT IGNORE INTO table_b SELECT * FROM table_a_delta;
    INSERT IGNORE INTO table_c SELECT * FROM table_a_delta;

    DROP TABLE table_a_delta;
END $$ 
DELIMITER ;

Then, schedule a MySQL Event on the Slave for the Stored Procedure to go off every 5 minutes starting 5 minutes after you create the event:

CREATE EVENT ev 
    ON SCHEDULE 
      EVERY 5 MINUTE 
      STARTS (NOW() + INTERVAL 5 MINUTE) 
    DO 
      CALL Load_New_Data();

Don't forget to Add This to the Slave's /etc/my.cnf

[mysqld]
event_scheduler=1

Best Answer

Related Solutions

Mysql – What are some good methods of structuring a MySQL audit trail to be both immutable, and perform well for usage

Mysql – Keeping 2 tables in sync

Related Question