views:

427

answers:

2

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?

+1  A: 

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:

Daniel Vassallo
care to link me to a tutorial? Thank you!
jiexi
RTM: http://dev.mysql.com/doc/refman/5.1/en/triggers.html
outis
where would i put this? Im using a phpmyadmin equivalent.sorry, im a noob :(
jiexi
also, what about updates, will this check be performed when rows are updated aswell?
jiexi
@jiexi: Check this question regarding Triggers and phpMyAdmin: http://stackoverflow.com/questions/2324924/where-i-write-mysql-trigger/... Update my answer with a trigger for UPDATEs as well.
Daniel Vassallo
This will *almost* work. MySQL does not support this `INSERT OR UPDATE` construct. You have to write 2 separate triggers. See my answer.
Roland Bouman
@Roland: Thanks for that. I noticed and modified my answer.
Daniel Vassallo
@Daniel: field `link_votes_total` needs to be prefixed with `NEW.`, otherwise it will be taken to be a system variable. The SET statement needs to be ended with a ';', which means you need to set a different delimiter and use it to end the `CREATE TRIGGER` statement.
outis
CREATE TRIGGER trig_mytable BEFORE INSERT ON pligg_links FOR EACH ROW BEGIN SET link_vote_total = NEW.link_votes - NEW.link_reports END; CREATE TRIGGER trig_mytable BEFORE UPDATE ON pligg_links FOR EACH ROW BEGIN SET link_vote_total = NEW.link_votes - NEW.link_reports END; failed : Unknown system variable 'link_vote_total'Im using virtualmin/webmin for my mysql stuff
jiexi
@jiexi: Check my updated answer. As outis noted, I had mistakenly omitted the `NEW.` prefix.
Daniel Vassallo
@jiexi: the reason is each new row is notionally stored in a table called `NEW` local to the trigger. `NEW.link_vote_total` is a field in this table; without the prefix, `link_vote_total` names a system variable (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html), which doesn't exist, hence the error.
outis
+1  A: 

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 ;
Roland Bouman
DELIMITER // CREATE TRIGGER bir_links BEFORE INSERT ON pligg_links FOR EACH ROW BEGIN SET link_vote_total = NEW.link_votes - NEW.link_reports; END; // CREATE TRIGGER bur_links BEFORE UPDATE ON pligg_links FOR EACH ROW BEGIN SET link_vote_total = NEW.link_votes - NEW.link_reports; END; // DELIMITER ; failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER // CREATE TRIGGER bir_links BEFORE INSERT ON pligg_links FOR EACH ROW ' at line 1Im using virtualmin/webmin btw
jiexi