MySQL5.1の「トリガ」について質問です。

(1)

前回と同様のテーブルで例にすると

TRIGGER等で書く場合は、tagについて

は処理が遅くなるので文字でなく数字

カラムにします。

tagの意味も評価をでソートにも使えるように

正の整数とします。

tagの意味

100 面白い

50 非常につまらない

0 その他

Table_Aに集計用にはtagの修正用のカラム

とtag更新日を追加したと仮定

rank1 ( tag=100 集計)

rank2 ( tag= 50 集計)

rank3 ( tag=100,50以外値集計)

rankdate (tagの登録された日)

alter table Table_A add rank1 int default 0;

alter table Table_A add rank2 int default 0;

alter table Table_A add rank3 int default 0;

alter table Table_A add rankdate datetime;

TRIGGERは動作確認しにくいので

処理は、PROCEDUREで作成する。

DELIMITER ||

CREATE TRIGGER TRIG_Insert AFTER INSERT ON Table_B

FOR EACH ROW BEGIN

CALL PROC_Add(NEW.book_id,NEW.tag);

END;

||

CREATE TRIGGER TRIG_Update AFTER UPDATE ON Table_B

FOR EACH ROW BEGIN

if OLD.tag != NEW.tag then

CALL PROC_Sub(OLD.book_id,OLD.tag);

CALL PROC_Add(NEW.book_id,NEW.tag);

end if;

END;

||

CREATE TRIGGER TRIG_Delete AFTER DELETE ON Table_B

FOR EACH ROW BEGIN

CALL PROC_Sub(OLD.book_id,OLD.tag);

END;

||

DELIMITER ;

PROCEDUREで各カラム処理を行う。

DELIMITER ||

create procedure PROC_Add(in in_book_id int,in in_tag int)

begin

declare setcol int default 3;

case in_tag

when 100 then set setcol=1;

when 50 then set setcol=2;

else set setcol=3;

end case;

update Table_A set rank1=IF(setcol=1,rank1+1,rank1),

rank2=IF(setcol=2,rank2+1,rank2),

rank3=IF(setcol=3,rank3+1,rank3),

rankdate=now()

where book_id = in_book_id;

end;

||

create procedure PROC_Sub(in in_book_id int,in in_tag int)

begin

declare setcol int default 3;

case in_tag

when 100 then set setcol=1;

when 50 then set setcol=2;

else set setcol=3;

end case;

update Table_A set rank1=IF(setcol=1,if(rank1>0,rank1-1,rank1),rank1),

rank2=IF(setcol=2,if(rank2>0,rank2-1,rank2),rank2),

rank3=IF(setcol=3,if(rank3>0,rank3-1,rank3),rank3)

where book_id = in_book_id;

END;

||

DELIMITER ;

動作確認は、SQLで

SELECT * FROM Table_A where book_id=1234;

CALL PROC_Add(1234,100);

SELECT * FROM Table_A where book_id=1234;

CALL PROC_Sub(1234,100);

SELECT * FROM Table_A where book_id=1234;

等すれば可能。

登録済みのデータや不整合に

なった場合のや、何らかの不具合で

再度値を計算しなおす処理

(プログラムで CALL PROC_Rebuildを呼び出す

か手動)

DELIMITER ||

create procedure PROC_Rebuild()

begin

update Table_A set rank1=0,rank2=0,rank3=0;

update Table_A as a

left join (select book_id,tag,count(tag) as tagc from Table_B where tag=100 group by book_id) as b

using(book_id) set rank1 =b.tagc where tagc is not null;

update Table_A as a

left join (select book_id,tag,count(tag) as tagc from Table_B where tag=50 group by book_id) as b

using(book_id) set rank2=b.tagc where tagc is not null;

update Table_A as a

left join (select book_id,tag,count(tag) as tagc from Table_B group by book_id) as b

on a.book_id=b.book_id set rank3=b.tagc-rank1-rank2 where tagc is not null;

end;

||

DELIMITER ;

例えば

RANK1が一番多いものを検索する場合は、

select * from Table_A where greatest(rank1,rank2,rank3)=rank1;

RANK1が一番少ないのを検索する場合は、

select * from Table_A where least(rank1,rank2,rank3)=rank1;

応用すればrankdateを検索条件に追加して

何日以内に更新があった場合なども可能。

(2)

SQLを手動で入力すれば全て可能

削除のしかた

drop procedure IF EXISTS 名前;

drop function IF EXISTS 名前;

drop trigger 名前;

登録の確認

select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_OBJECT_TABLE,ACTION_TIMING from information_schema.TRIGGERS;

select db,name,type,specific_name from mysql.proc order by type;

定義済み内容の確認

show create procedure 名前;

show create function 名前;

show triggers

ERRORを見る

show errors;

show warnings;

show logs;

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