delimiter $$
--DROP TRIGGER /*!50032 IF EXISTS */ `sum_sale`$$
create trigger sum_sale after insert on cu_order for each row
begin
DECLARE sales decimal(10,2) DEFAULT (SELECT SUM( order_amount ) as a FROM `cu_order` WHERE stores=new.stores AND state >1);
DECLARE no_more_user INT DEFAULT 0;
DECLARE user_id INT(11);
DECLARE cur_user CURSOR FOR SELECT id FROM employ_user WHERE user_state=1 AND ( dept_id=new.stores or dept_id in (SELECT id FROM `employ_dept` WHERE `company_id`=1 AND `type`=1 AND `state`=1) ) ; /*First: Delcare a cursor,首先这里对游标进行定义*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_user = 1; /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/
IF sales > 1000000 THEN
OPEN cur_user; /*Second: Open the cursor 接着使用OPEN打开游标*/
FETCH cur_user INTO user_id; /*Third: now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了记录的第一行*/
REPEAT
INSERT INTO `ser_message` (`id`, `uid`, `touid`, `title`, `content`, `url`, `state`, `type`, `time`, `remind_time`, `note`) VALUES (NULL, '0', user_id, '喜报！', '喜报！恭喜门店销售额突破100万！', NULL, '1', '1', CURRENT_TIMESTAMP, '0', NULL);
FETCH cur_user INTO user_id;
UNTIL no_more_user = 1 END REPEAT;
CLOSE cur_user; /*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/
END IF;
end;
$$
delimiter ;
代码说明：
当订单总金额超过100W则通知相关用户