1

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?

  • Accept that this is one of the costs of adding columns. – Rick James Aug 25 '15 at 20:27
3

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?

  • Thanks for your answer, what exactly do you mean by "you're going to have to keep track of maintenance of the AuditCompany table anyway" ? Why would a maintenance be needed? – Urbanleg Aug 25 '15 at 7:20
  • @Urbanleg - what I meant by that is that anytime you modify your Company table, you're also going to have to modify the audit table, so why not modify the trigger at the same time - if necessary? – Vérace Aug 25 '15 at 17:02
  • Oh, You are quiet right about it. Thanks for your answers verace – Urbanleg Aug 25 '15 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.