Mysql auto fill field based on value of two other fields?

Question

I know its possible to autoincrement values, but i was wondering if its possible to fill a field based on the value of two other fields. I have a table with the fields:

CREATE TABLE pligg_links (
  ...
  link_votes INT,
  link_reports INT,
  link_votes_total INT,
  ...
);

Field link_votes_total should hold the value of field link_votes subtracted from link_reports. So basically, this is the math equation: link_votes_total = link_votes - link_reports. Is this possible without having to use php to do it before data is stored?

Solution 1

Yes, this can be done by creating a trigger for BEFORE INSERT and another one for BEFORE UPDATE:

DELIMITER //

CREATE TRIGGER trig_mytable BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    SET NEW.link_votes_total = NEW.link_votes - NEW.link_reports;
END
//

CREATE TRIGGER trig_mytable BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
    SET NEW.link_votes_total = NEW.link_votes - NEW.link_reports;
END
//

DELIMITER ;

Further Reading:

• MySQL 5.1 Reference Manual: CREATE TRIGGER Syntax

Solution 2

See:http://dev.mysql.com/doc/refman/5.1/en/triggers.html

DELIMITER //

CREATE TRIGGER bir_links
BEFORE INSERT ON links
FOR EACH ROW 
BEGIN
    SET link_votes_total = NEW.link_votes - NEW.link_reports;
END;
//

CREATE TRIGGER bur_links
BEFORE UPDATE ON links
FOR EACH ROW 
BEGIN
    SET link_votes_total = NEW.link_votes - NEW.link_reports;
END;
//

DELIMITER ;