关于SQL server触发器编程问题

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

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

图1

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

图2

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

图3

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

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

图4

3个回答

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 触发器,看看是否有参考价值。

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tr_WorkOrderInfo_i]') AND OBJECTPROPERTY(id,N'IsTrigger') = 1)
DROP TRIGGER [tr_WorkOrderInfo_i]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.tr_WorkOrderInfo_i ON dbo.WorkOrderInfo
FOR INSERT AS
BEGIN
-- 判断有没有数据
IF EXISTS (SELECT 0 FROM Inserted)
BEGIN
DECLARE @partnerAbbr NVARCHAR(40)
,@partnerCode NVARCHAR(40)
,@flag1 INT

           ,@count       INT
           ,@i           INT = 1
           ,@contactId   INT

    SELECT @count = COUNT(*) FROM Inserted

    -- 循环新增到 dbo.Contact 中
    WHILE @i <= @count
    BEGIN

        SELECT 
               @partnerCode = T2.PartnerCode
              ,@partnerAbbr = T2.PartnerAbbr
              ,@flag1       = T2.Flag1
          FROM 
              (SELECT 
                      RowNumber = ROW_NUMBER() OVER(ORDER BY T1.PartnerCode)
                     ,* 
                 FROM 
                      (SELECT 
                              Inserted.PartnerCode
                             ,Inserted.PartnerAbbr
                             ,Inserted.Flag1 
                         FROM 
                              Inserted
                      GROUP BY
                              Inserted.PartnerCode,Inserted.PartnerAbbr,Inserted.Flag1) T1
               ) T2               
         WHERE 
               T2.RowNumber = @i

        -- 判断该厂商是否存在
        IF NOT EXISTS (SELECT 0 FROM dbo.Contact WHERE Code = @partnerCode AND Name = @partnerAbbr AND Flag1 = @flag1)
        BEGIN

        -- 获取厂商的Id
        SELECT @contactId = ISNULL((MAX(Id) + 1),200000) FROM dbo.Contact WHERE Flag1 != 0

        SET IDENTITY_INSERT dbo.Contact ON

           INSERT INTO dbo.Contact
                     ( Id ,
                       Code ,
                       Name ,
                       Nick ,
                       EnglishName ,
                       Title ,
                       Department ,
                       Role ,
                       Remark ,
                       Tel1 ,
                       Tel2 ,
                       Tel3 ,
                       Tel4 ,
                       Tel5 ,
                       TelMap ,
                       Email1 ,
                       Email2 ,
                       Email3 ,
                       Email4 ,
                       Email5 ,
                       EmailMap ,
                       Address1Id ,
                       Address2Id ,
                       Address3Id ,
                       AddressMap ,
                       StrExt1 ,
                       StrExt2 ,
                       NumExt1 ,
                       NumExt2 ,
                       Flag1 ,
                       Flag2 ,
                       IsActive ,
                       CreatedDate ,
                       CreatedBy ,
                       ModifiedDate ,
                       ModifiedBy ,
                       State
                     )
             VALUES  ( @contactId ,
                       @partnerCode , -- Code - nvarchar(40)
                       @partnerAbbr , -- Name - nvarchar(40)
                       N'' , -- Nick - nvarchar(40)
                       N'' , -- EnglishName - nvarchar(40)
                       0 , -- Title - int
                       0 , -- Department - int
                       0 , -- Role - int
                       N'' , -- Remark - nvarchar(1024)
                       N'' , -- Tel1 - nvarchar(20)
                       N'' , -- Tel2 - nvarchar(20)
                       N'' , -- Tel3 - nvarchar(20)
                       N'' , -- Tel4 - nvarchar(20)
                       N'' , -- Tel5 - nvarchar(20)
                       N'' , -- TelMap - nvarchar(80)
                       N'' , -- Email1 - nvarchar(40)
                       N'' , -- Email2 - nvarchar(40)
                       N'' , -- Email3 - nvarchar(40)
                       N'' , -- Email4 - nvarchar(40)
                       N'' , -- Email5 - nvarchar(40)
                       N'' , -- EmailMap - nvarchar(80)
                       0 , -- Address1Id - int
                       0 , -- Address2Id - int
                       0 , -- Address3Id - int
                       N'' , -- AddressMap - nvarchar(80)
                       N'' , -- StrExt1 - nvarchar(40)
                       N'' , -- StrExt2 - nvarchar(80)
                       0.00000 , -- NumExt1 - decimal
                       0.00000 , -- NumExt2 - decimal
                       @flag1 , -- Flag1 - int
                       0 , -- Flag2 - int
                       1 , -- IsActive - int
                       GETDATE() , -- CreatedDate - smalldatetime
                       1 , -- CreatedBy - int
                       GETDATE() , -- ModifiedDate - smalldatetime
                       1 , -- ModifiedBy - int
                       1  -- State - int
                     )

        SET IDENTITY_INSERT dbo.Contact OFF

        END

        SET @i = @i + 1

     END
END

END

wodeqq584958184
wodeqq584958184 好的 我参考下
一年多之前 回复
Jason_Brown
JellyMonster 这是我写过的一个sqlserver的触发器,听你描述 感觉只需要在 Inserted表里 根据时间排序获取第一条记录,再插到表中
一年多之前 回复

在table2 上添加触发器,每次插入触发,

查一下 table1 最近一次宕机的时间 和 table2 最后一行错误代码的时间 对比,若小于宕机时间就不采集

具体触发器语法你得学习一下,打开 数据库的 error.log 看看触发器错误。

我没用过sqk server,是以Mysql 角度来答复这个问题的。

wodeqq584958184
wodeqq584958184 还有我发现了一个问题,可能需要加Q详谈
一年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!