关于SQL server触发器编程问题

Question

本人目前处于实习生，公司给了我一个项目，项目进度快接近完成了，无奈遇到了个大问题，真的不知道该怎么解决，公司又没人带，我上司也只是懂一点点，但不是专家，他是学VS编程的，看看有没有SQL 大神或专家帮我分析和解决下这问题，可能想了解的更详细可以加我QQ:584958184.

现在公司有台机器,机器的状态有0,1,2,3,机器开启运行状态的时候是0,其他的 1、2和3对应的是其他状态,机器一开启就会自动有数据生成,生成的数据信息如（图1）,

down机的时候,(也就是从0变为非0的时候，也就是0到1，或0到2，或者0到3这样的状态)table2会有数据记录产生，产生的数据记录其实就是错误代码。而每一次table1发生down机状态记录的时候，都会有个故障时间数据产生(即table1的非0数据记录的itime字段)，故障了那么table2肯定会插入错误代码，但在这一个故障时间段直到下次机器变为0状态恢复运行时，错误代码有很多，例如下图是其中一个table1的down机过程：

下图就是table2造成此次down机的所有错误代码

由此可见，此次down机状态的时间是13点58分35秒，并且在13点59分22秒机器恢复运行(table2里runtime字段时间其实就是机器下次恢复运行变为0的时间)，这2秒的时间里造成此次down机的错误代码有许多条，并且有些错误代码都在一个时间点上13点58分36秒，但有些不是，

现在我上司想要实现的效果就是，table1的每一次down机状态，我只要table2相对应此次down机状态的最先报错第一条代码，其他错误代码其他时间都不要，只要一条，并把此条错误代码数据记录的location字段和pointer字段还有itime值插入到table4中，效果如下图（图中效果的数据记录是手动插入）：

Answer 1

BEGIN
         CREATE TEMPORARY TABLE IF NOT EXISTS t4(
                    flag VARCHAR(1),
            licensetype VARCHAR(2),
            carno VARCHAR(16),
            carno_original  VARCHAR(16),
            pointcodeEnt VARCHAR(12),
            pointcodeExt VARCHAR(12),
            pointnameEnt  VARCHAR(100),
            pointnameExt  VARCHAR(100),
            devicecode VARCHAR(18),
            devicecodeExt VARCHAR(18),
            cpicEnterPath  VARCHAR(200),
            cpicExitPath   VARCHAR(200),
            carcolor VARCHAR(4),
                plate_position VARCHAR(45),
            collectiondate1 DATETIME,
            collectiondate2 DATETIME
        );

                SET @flag:= '1';
        INSERT INTO t4 SELECT
                            @flag:= '2' AS flag,
                @licensetype:=IFNULL(t2.licensetype,t3.licensetype) AS licensetype,
                @carno:= IFNULL(t2.carno,t3.carno) AS carno,
                @carno_original:= IFNULL(t2.carno_original,t3.carno_original) AS carno_original,
                @pointcodeEnt:=IFNULL(t2.pointcode,'') AS pointcodeEnt,
                @pointcodeExt:=IFNULL(t3.pointcode,'') AS pointcodeExt,
                @pointnameEnt:=IFNULL(t2.pointname,'') AS pointnameEnt,
                @pointnameExt:=IFNULL(t3.pointname,'') AS pointnameExt,
                @devicecode:=IFNULL(t2.devicecode,'') AS devicecode,
                @devicecodeExt:=IFNULL(t3.devicecode,'') AS devicecodeExt,
                @cpicEnterPath:=t2.cpic1path AS cpicEnterPath,
                @cpicExitPath:=t3.cpic1path AS cpicExitPath,
                @carcolor:=IFNULL(t2.carcolor,t3.carcolor) AS carcolor,
                @plate_position:=IFNULL(t2.plate_position,t3.plate_position) AS plate_position,
                @collectiondate1:=IFNULL(t2.collectiondate,NULL) AS collectiondate1,   -- 入口采集时间
                @collectiondate2:=IFNULL(t3.collectiondate,NOW()) AS collectiondate2
            FROM  park_detect_info t2
                INNER JOIN park_detect_info t3 ON NEW.exit_nid = t3.nid
            WHERE  t2.nid = NEW.enter_nid;

                IF @flag = '1' THEN
            INSERT INTO t4 SELECT
                    @flag:= '2' AS falg,
                    @licensetype:= t2.licensetype AS licensetype,
                    @carno:= t2.carno AS carno,
                    @carno_original:= t2.carno_original AS carno_original,
                    @pointcodeEnt:= '' AS pointcodeEnt,
                    @pointcodeExt:= IFNULL(t2.pointcode,'') AS pointcodeExt,
                    @pointnameEnt:= '' AS pointnameEnt,
                    @pointnameExt:= IFNULL(t2.pointname,'') AS pointnameExt,
                    @devicecode:= '' AS devicecode,
                    @devicecodeExt:= IFNULL(t2.devicecode,'') AS devicecodeExt,
                    @cpicEnterPath:= '' AS cpicEnterPath,
                    @cpicExitPath:= t2.cpic1path AS cpicExitPath,
                    @carcolor:= t2.carcolor AS carcolor,
                    @plate_position:=t2.plate_position AS plate_position,
                    @collectiondate1:= NULL AS collectiondate1,   -- 入口采集时间
                    @collectiondate2:= t2.collectiondate AS collectiondate2
                FROM  park_detect_info t2  WHERE  t2.nid = NEW.exit_nid;         -- 如果出口存在，则用出口的
                END IF;  

                        IF @flag = '1' THEN
            INSERT INTO t4 SELECT
                    @flag:= '2' AS falg,
                    @licensetype:=t2.licensetype AS licensetype,
                    @carno:= t2.carno AS carno,
                    @carno_original:= t2.carno_original AS carno_original,
                    @pointcodeExt:='' AS pointcodeExt,
                    @pointcodeEnt:=IFNULL(t2.pointcode,'') AS pointcodeEnt,
                    @pointnameEnt:=IFNULL(t2.pointname,'') AS pointnameEnt,
                    @pointnameExt:='' AS pointnameExt,
                    @devicecode:=IFNULL(t2.devicecode,'') AS devicecode,
                    @devicecodeExt:='' AS devicecodeExt,
                    @cpicEnterPath:=t2.cpic1path AS cpicEnterPath,
                    @cpicExitPath:='' AS cpicExitPath,
                    @carcolor:=t2.carcolor AS carcolor,
                    @plate_position:=t2.plate_position AS plate_position,
                    @collectiondate1:=t2.collectiondate AS collectiondate1,   
                    @collectiondate2:=NULL AS collectiondate2
                FROM  park_detect_info t2  WHERE t2.nid = NEW.enter_nid;          -- 如果出口不存在，则用入口的
        END IF;         

        IF @flag = '2' THEN 
            SET NEW.`licensetype` = @licensetype; SET @licensetype:= NULL;
            SET NEW.carno = @carno; SET @carno:= NULL;
            SET NEW.carno_original = @carno_original;SET @carno_original:= NULL;
            SET NEW.pointcodeEnt = @pointcodeEnt; SET @pointcodeEnt:= NULL;  SET NEW.pointcodeExt = @pointcodeExt;SET @pointcodeExt:= NULL;
            SET NEW.pointnameEnt = @pointnameEnt; SET @pointnameEnt:= NULL;  SET NEW.pointnameExt = @pointnameExt;SET @pointnameExt:= NULL;
            SET NEW.devicecode = @devicecode;SET @devicecode:= NULL;
            SET NEW.devicecodeExt = @devicecodeExt;SET @devicecodeExt:= NULL;
            SET NEW.cpicEnterPath = @cpicEnterPath; SET @cpicEnterPath:= NULL;
            SET NEW.cpicExitPath = @cpicExitPath;SET @cpicExitPath:= NULL;
            SET NEW.carcolor = @carcolor;SET @carcolor:= NULL;
            SET NEW.plate_position = @plate_position;SET @plate_position:= NULL;
            SET NEW.collectiondate1 = @collectiondate1;SET @collectiondate1:= NULL;
            SET NEW.collectiondate2 = @collectiondate2;SET @collectiondate2:= NULL;
          SET @flag:= '1';
      END IF; 

        DELETE FROM t4 WHERE 1=1;

END

以前写过的一个坑爹的mysql 触发器，看看是否有参考价值。