关于SQL server触发器编程问题
本人目前处于实习生，公司给了我一个项目，项目进度快接近完成了，无奈遇到了个大问题，真的不知道该怎么解决，公司又没人带，我上司也只是懂一点点，但不是专家，他是学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中，效果如下图（图中效果的数据记录是手动插入）：
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 触发器，看看是否有参考价值。
在table2 上添加触发器，每次插入触发，
查一下 table1 最近一次宕机的时间 和 table2 最后一行错误代码的时间 对比，若小于宕机时间就不采集
具体触发器语法你得学习一下，打开 数据库的 error.log 看看触发器错误。
我没用过sqk server，是以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