Mysql triggers for auditing

audithibernateMySQLtrigger

We have a requirement to fulfill – we need an auditing table for all of our entities.
We wonder if creating the following triggers will be a good practice:

CREATE DEFINER=`root`@`localhost` TRIGGER `admin`.`company_AFTER_UPDATE` AFTER UPDATE ON `company` FOR EACH ROW
 BEGIN
  INSERT INTO AuditCompany  
   select *, now() from Company where id = NEW.id;
 END

1) Is this a good practice?

2) Is there a better way of doing this?

3) We wanted to avoid the 'insert into XXX values (NEW.id, NEW.col1, NEW.col2) thats why this trigger is doing a subselect on its own – this will remove the need to update the trigger on every new column in the entity. But is it ok?

Best Answer

That will work fine - however you need to add a delimiter to your trigger:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `admin`.`company_AFTER_UPDATE` AFTER UPDATE ON `company` FOR EACH ROW
 BEGIN
  INSERT INTO AuditCompany  
   select *, now() from Company where id = NEW.id;
 END $$
DELIMITER ;

You might gain some performance benefit if you kept the fields in your audit table to a minimum. You would also save space, although these days, that's less of an issue.

And don't forget to have an ON_INSERT and an ON_DELETE trigger also.

[EDIT].

Two further issues have occurred to me:

1) You say that "We wanted to avoid the 'insert into XXX values (NEW.id,....", but you're going to have to keep track of maintenance of the AuditCompany table anyway, which is perhaps a reason to be selective about the fields that you audit.

2) There are Open Source audit plugins in which you might be interested - Percona and McAfee. MariaDB do one - but I don't think that the source is available. Perhaps you can automate some of this process which appears to be what you would like to do?

Related Question