wodeqq584958184
2018-04-24 01:31
采纳率: 77.8%
浏览 1.1k

关于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条回答 默认 最新

  • Haisen大王 2018-04-24 01:51
    已采纳
    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 触发器,看看是否有参考价值。

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • Haisen大王 2018-04-24 01:43

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

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

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

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

    评论
    解决 无用
    打赏 举报
  • JellyMonster 2018-04-24 03:40

    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

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题