投票とかで使うトリガ

投票とかで使うトリガ

http://asakura.g.hatena.ne.jp/asakura-t/20080227/1204101591

　毎回カウントするトリガ

CREATE TRIGGER _vote_after_insert
  AFTER INSERT ON vote FOR EACH ROW
    REPLACE INTO vote_count
    SET target_id   = NEW.target_id,
        total_count = (SELECT COUNT(*) FROM vote WHERE target_id = NEW.target_id)
//

――は、件数が多くなってくると（InnoDBだとさらに）結構重くなるので、

DELIMITER //
CREATE PROCEDURE `_vote_count_inc`( _target_id int )
BEGIN
  DECLARE _exist int DEFAULT NULL;
  SELECT total_count INTO _exist
    FROM vote_count WHERE target_id = _target_id;
  IF _exist THEN
    UPDATE vote_count
    SET total_count = total_count + 1
    WHERE target_id = _target_id
    ;
  ELSE
    INSERT INTO vote_count
    SET target_id = _target_id,
        total_count = ( SELECT COUNT(*) FROM vote WHERE target_id = _target_id )
    ;
  END IF;
END
//
CREATE TRIGGER _vote_after_insert
  AFTER INSERT ON vote FOR EACH ROW
  CALL _vote_count_inc( NEW.target_id )
//

――にしたほうがよさげ。

　件数が少ないときはSELECT COUNT(*)してもあまり気にならなかったけど、件数が増えると格段に重くなります……。