YRniYLVc

1CREATE TABLE IF NOT EXISTS `muser_summary_points` (
2	`user_id` int(10) unsigned NOT NULL,
3	`all_points` int(10) NOT NULL,
4	`created` datetime NOT NULL,
5	`modified` datetime NOT NULL,
6	PRIMARY KEY (`user_id`)
7) ENGINE=InnoDB DEFAULT CHARSET=utf8;
8
9CREATE TABLE  IF NOT EXISTS `muser_points` (
10`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
11`user_id` int(10) unsigned NOT NULL,
12`user_review_id` int(10) unsigned,
13`product_id` int(10) unsigned NOT NULL,
14`points` int(10) NOT NULL,
15`type` VARCHAR(30) NOT NULL,
16`created` datetime NOT NULL,
17`modified` datetime NOT NULL,
18 PRIMARY KEY (`id`)
19 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
20
21DELIMITER $$
22
23CREATE TRIGGER points_update
24    AFTER INSERT ON `muser_points`
25    FOR EACH ROW
26BEGIN
27
28
29INSERT INTO
30muser_summary_points  (user_id, all_points, created, modified)
31
32SELECT * FROM (
33SELECT NEW.user_id, SUM(points) as sum_points, NOW() as date_created, NOW() as date_modified
34       FROM muser_points mp
35      WHERE mp.user_id = NEW.user_id) as u
36ON DUPLICATE KEY UPDATE
37muser_summary_points.all_points = u.sum_points, muser_summary_points.modified = NOW();
38
39END $$