mysql解题器_mysql触发器，答题记录表同步教学跟踪（用户列表）

最新推荐文章于 2024-04-18 16:53:46 发布

Victor.ZZ

最新推荐文章于 2024-04-18 16:53:46 发布

阅读量91

点赞数

文章标签： mysql解题器

本文链接：https://blog.csdn.net/weixin_42392054/article/details/113295788

版权

DELIMITER ||

DROP TRIGGER IF EXISTS t_afterinsert_on_tab1 ||

CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT

ON online_answerrecord FOREACH ROWBEGIN

--判断数据库中有无此记录，有，修改，无，新增

SET @count =(SELECT

COUNT(1)FROMonline_papercompletion_userWHERE USERID =new.useridAND 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 INTOonline_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);

UPDATEonline_papercompletion_userSET

--personlname,--sysuserid,--sysusername,--registrationsituation,--curriculum_name,--examinationtype,--examinationdate,--examinationaddress,--phonenumber,--z_totalquestions,

z_totalcomplete =(SELECTc.azFROM(

(SELECT

CONVERT(z_totalcomplete, SIGNED) azFROMonline_papercompletion_userWHERE userid =new.useridAND orderid =new.orderid)

) c)+ CAST(@zhangjiewancheng ASSIGNED),--q_totalquestions,

q_totalcomplete =(SELECTz.azFROM(

(SELECT

CONVERT(q_totalcomplete, SIGNED) azFROMonline_papercompletion_userWHERE userid =new.useridAND orderid =new.orderid)

) z)+ CAST(@qianghuawancheng ASSIGNED),--c_totalquestions,

c_totalcomplete =(SELECTc.azFROM(

(SELECT

CONVERT(c_totalcomplete, SIGNED) azFROMonline_papercompletion_userWHERE userid =new.useridAND orderid =new.orderid)

) c)+ CAST(@yucewancheng ASSIGNED),--bysituation,--score,

z_totaltrue =(SELECTc.azFROM(

(SELECT

CONVERT(z_totaltrue, SIGNED) azFROMonline_papercompletion_userWHERE userid =new.useridAND orderid =new.orderid)