如果所示,根据订单编号和用户编号判断唯一,第一次是新增,以后是修改
DELIMITER || DROP TRIGGER IF EXISTS t_afterinsert_on_tab1 || CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON online_answerrecord FOR EACH ROW BEGIN -- 判断数据库中有无此记录,有,修改,无,新增 SET @count = (SELECT COUNT(1) FROM online_papercompletion_user WHERE USERID = new.userid AND ORDERID = new.orderid) ; -- 设置考试类型 -- SET @examinationtype = new.examinationtype; -- 强化总题数量 -- SET @qianghuatype = 0; -- 章节总题数量 -- SET @zhangjietype = 0; -- 预测总题数量 -- SET @yucetype = 0; -- 章节完成数量 SET @zhangjiewancheng = 0 ; -- 强化完成数量 SET @qianghuawancheng = 0 ; -- 预测完成数量 SET @yucewancheng = 0 ; -- 是否正确 -- SET @iscorrect = 0; -- 设置章节试题(正确数) SET @zhangjiezhengque = 0 ; -- 设置章节错误(错误数) SET @zhangjiecuowu = 0 ; -- 强化试题(正确数) SET @zhangjiezhengque = 0 ; -- 强化试题(错误数) SET @qianghuacuowu = 0 ; -- 预测试题(正确数) SET @yucezhengque = 0 ; -- 预测试题(错误数) -- set @yucecuowu = 0 ; -- 如果答题正确 暂定0是正确 -- IF new.Iscorrect = '0' THEN -- ( IF new.examinationtype = '强化试卷' AND new.Iscorrect = '0' THEN SET @qianghuawancheng = '1' ; SET @qianghuazhengque = '1' ; ELSEIF new.examinationtype = '章节试卷' AND new.Iscorrect = '0' THEN SET @zhangjiewancheng = '1' ; SET @zhangjiezhengque = '1' ; ELSEIF new.examinationtype = '预测试卷' AND new.Iscorrect = '0' THEN SET @yucewancheng = '1' ; SET @yucezhengque = '1' ; ELSEIF new.examinationtype = '强化试卷' AND new.Iscorrect = '1' THEN SET @qianghuawancheng = '1' ; SET @qianghuacuowu1 = '1' ; ELSEIF new.examinationtype = '章节试卷' AND new.Iscorrect = '1' THEN SET @zhangjiewancheng = '1' ; SET @zhangjiecuowu = '1' ; ELSEIF new.examinationtype = '预测试卷' AND new.Iscorrect = '1' THEN SET @yucewancheng = '1' ; SET @yucecuowu = '1' ; END IF ; -- ) -- END if; IF @count = 0 THEN INSERT INTO online_papercompletion_user ( id, name1, name2, createtime, userid, personlname, sysuserid, sysusername, registrationsituation, curriculum_name, examinationtype, examinationdate, examinationaddress, phonenumber, z_totalquestions, z_totalcomplete, q_totalquestions, q_totalcomplete, c_totalquestions, c_totalcomplete, bysituation, score, orderid, z_totaltrue, z_totalfalse, q_totaltrue, q_totalfalse, c_totaltrue, c_totalfalse ) VALUES ( FLOOR(1 + (RAND() * 400000)), new.name1, new.name2, NOW(), new.USERID, '', '', '', '', '', new.examinationtype, NOW(), '', '', '', @zhangjiewancheng, '', @qianghuawancheng, '', @yucewancheng, '', '', new.orderid, @zhangjiezhengque, @zhangjiecuowu, @qianghuazhengque, @qianghuacuowu, @yucezhengque, @yucecuowu ) ; ELSEIF @count > 0 THEN -- set @zwancheng = cast((SELECT z_totalcomplete FROM online_papercompletion_user WHERE userid=new.userid AND orderid=new.orderid) as bigint); UPDATE online_papercompletion_user SET -- personlname, -- sysuserid, -- sysusername, -- registrationsituation, -- curriculum_name, -- examinationtype, -- examinationdate, -- examinationaddress, -- phonenumber, -- z_totalquestions, z_totalcomplete = (SELECT c.az FROM ( (SELECT CONVERT(z_totalcomplete, SIGNED) az FROM online_papercompletion_user WHERE userid = new.userid AND orderid = new.orderid) ) c) + CAST(@zhangjiewancheng AS SIGNED), -- q_totalquestions, q_totalcomplete = (SELECT z.az FROM ( (SELECT CONVERT(q_totalcomplete, SIGNED) az FROM online_papercompletion_user WHERE userid = new.userid AND orderid = new.orderid) ) z) + CAST(@qianghuawancheng AS SIGNED), -- c_totalquestions, c_totalcomplete = (SELECT c.az FROM ( (SELECT CONVERT(c_totalcomplete, SIGNED) az FROM online_papercompletion_user WHERE userid = new.userid AND orderid = new.orderid) ) c) + CAST(@yucewancheng AS SIGNED), -- bysituation, -- score, z_totaltrue = (SELECT c.az FROM ( (SELECT CONVERT(z_totaltrue, SIGNED) az FROM online_papercompletion_user WHERE userid = new.userid AND orderid = new.orderid) ) c) + CAST(@yucewancheng AS SIGNED), z_totalfalse = (SELECT c.az FROM ( (SELECT CONVERT(z_totalfalse, SIGNED) az FROM online_papercompletion_user WHERE userid = new.userid AND orderid = new.orderid) ) c) + CAST(@yucewancheng AS SIGNED), q_totaltrue = (SELECT c.az FROM ( (SELECT CONVERT(q_totaltrue, SIGNED) az FROM online_papercompletion_user WHERE userid = new.userid AND orderid = new.orderid) ) c) + CAST(@yucewancheng AS SIGNED), q_totalfalse = (SELECT c.az FROM ( (SELECT CONVERT(q_totalfalse, SIGNED) az FROM online_papercompletion_user WHERE userid = new.userid AND orderid = new.orderid) ) c) + CAST(@yucewancheng AS SIGNED), c_totaltrue = (SELECT c.az FROM ( (SELECT CONVERT(c_totaltrue, SIGNED) az FROM online_papercompletion_user WHERE userid = new.userid AND orderid = new.orderid) ) c) + CAST(@yucewancheng AS SIGNED), c_totalfalse = (SELECT c.az FROM ( (SELECT CONVERT(c_totalfalse, SIGNED) az FROM online_papercompletion_user WHERE userid = new.userid AND orderid = new.orderid) ) c) + CAST(@yucewancheng AS SIGNED) WHERE userid = new.userid AND orderid = new.orderid ; END IF ; END || DELIMITER ; -- 测试数据 INSERT INTO zxks.online_answerrecord ( ANSWERRECORD_ID, CREATETIME, USERID, ORDERID, EXAMINATIONTYPE, QUALIFICATIONS_ID, CURRICULUM_ID, ZID, JID, MID, TID, STRUCTUREPATH_NAME, STRUCTUREPATH_CODE, QUESTIONID, ISCORRECT, XUANXIANG, PAGEID, ISTAG, FACILITYVALUE, NAME1, NAME2 ) VALUES ( '2', NOW(), '1', '1', '章节试卷', '1', '1', '1', '1', '1', '1', '1', '1', '1', '0', '1', '1', 1, '1', '1', '1' ) ;