MySQL – Triggers for auditing

[adsense id=”0514458240″ width=”468″ height=”60″]

Triggers are ideal for performing auditing. A trigger is database code that gets executed when either an INSERT, UPDATE or DELETE occurs on a particular table. Let’s imagine we have a pricing table which we want to make sure that keep an audit or everytime a price change happens in that table.

For this example we have a table called “products” which consists of numerous fields including “price”.

CREATE TABLE products (
  product_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_code VARCHAR(5),
  name VARCHAR(255),
  description TEXT,
  price DECIMAL(10,2)
) ENGINE = InnoDB;

We want to make sure that we keep track of the changes. The audit should consist of the product identifier, the operation performed i.e. INSERT, UPDATE or DELETE, a timestamp, the new price and a user.

As such we want our audit table to look like:

CREATE TABLE products_aud (
  product_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  operation CHAR(1),
  transdate DATETIME,
  price DECIMAL(10,2),
  user VARCHAR(30)
) ENGINE = InnoDB;

To refer to the trigger values in the trigger code we use NEW.field and OLD.field. In the example above, during the insert we want to access the NEW.price field, during the UPDATE we want to compare the NEW.price and the OLD.price to make sure that they are different.

In the example above we want to also include a “user” in the audit which is not included in the INSERT, UPDATE or DELETE statement. In this case here we can set a user defined variable which includes the name. This should be set somewhere after the database connection and before the insert into the table.

SET @username := 'admin';

Using this information we can now complete the code for the trigger:

DELIMITER $$

CREATE DEFINER=CURRENT_USER TRIGGER products_aud_ins
BEFORE INSERT
ON products
FOR EACH ROW
BEGIN
  INSERT INTO products_aud (product_id, transdate, operation, price, adminuser)
  VALUES (NEW.PRODUCT_ID, NOW(), 'I', NEW.PRICE, @username);
END;
$$

CREATE DEFINER=CURRENT_USER TRIGGER products_aud_upd
BEFORE UPDATE
ON products
FOR EACH ROW
BEGIN
  IF NEW.QUANTITY != OLD.QUANTITY OR NEW.PRICE != OLD.PRICE THEN
    INSERT INTO products_aud (product_id, transdate, operation, price, adminuser)
    VALUES (NEW.PRODUCT_ID, NOW(), 'U', NEW.PRICE, @username);
  END IF;
END;
$$

CREATE DEFINER=CURRENT_USER TRIGGER products_aud_del
BEFORE DELETE
ON products
FOR EACH ROW
BEGIN
  INSERT INTO products_aud (product_id, transdate, operation, price, adminuser)
  VALUES (OLD.PRODUCT_ID, NOW(), 'D', OLD.PRICE, @username);
END;
$$