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

 

如果所示，根据订单编号和用户编号判断唯一，第一次是新增，以后是修改

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'
  ) ;