各位大侠帮看看,触发器问题
workposition1、workposition2、workposition3是实验台的三个检测工位，每次检测数据自动插入各个检测工位对应的数据库，同车号的车辆多次检测的数据釆用插入的方式存入数据库，同一车号可能有多次检测数据。现要求将workposition1、workposition2、workposition3三个表中，同一车号的最后一次检测数据通过触发器存入report表中对应的字段。
CREATE TABLE [dbo].[P_workposition1] (
[主键] [float] NOT NULL ,
[测试员工号] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[叉车条形码] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[叉车类型] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[前进一档速度] [real] NULL ,
[倒退一档速度] [real] NULL ,
[行进电机稳态电流] [real] NULL ,
[电池电压] [real] NULL ,
[测试时间] [smalldatetime] NOT NULL ,
[备注] [char] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[P_workposition2] (
[主键] [float] NOT NULL ,
[测试员工号] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[叉车条形码] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[叉车类型] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[手左制动力] [real] NOT NULL ,
[手右制动力] [real] NOT NULL ,
[脚左制动力] [real] NOT NULL ,
[脚右制动力] [real] NOT NULL ,
[手制动力和] [real] NOT NULL ,
[脚制动力和] [real] NOT NULL ,
[手制动力差] [real] NOT NULL ,
[脚制动力差] [real] NOT NULL ,
[左阻滞力] [real] NOT NULL ,
[右阻滞力] [real] NOT NULL ,
[测试时间] [datetime] NOT NULL ,
[备注] [char] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[P_workposition3] (
[主键] [float] NOT NULL ,
[测试员工号] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[叉车条形码] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[叉车类型] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[满载门架起升速度] [real] NULL ,
[满载门架下降速度] [real] NULL ,
[起升电机最大电流] [real] NULL ,
[起升电机稳态电流] [real] NULL ,
[转向电机最大电流] [real] NULL ,
[转向电机稳态电流] [real] NULL ,
[测试时间] [datetime] NOT NULL ,
[备注] [char] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[report] (
[forkNo] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , ' [叉车条形码]
[forkType] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , ' [叉车类型]
[tester] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , ' [测试员工号]
[forwardSpeed1] [real] NULL , '[前进一档速度]
[revSpeed1] [real] NULL , '[倒退一档速度]
[steadyI] [real] NULL , '[行进电机稳态电流]
[batteryV] [real] NULL , '[电池电压]
[LHandBrake] [real] NULL , '[手左制动力]
[RHandBrake] [real] NULL , '[手右制动力]
[LFootBrake] [real] NULL , '[脚左制动力]
[RFootBrake] [real] NULL , '[脚右制动力]
[handBrakeSum] [real] NULL , '[手制动力和]
[handBrakeMinus] [real] NULL , '[手制动力差]
[footBrakeSum] [real] NULL , '[脚制动力和]
[footBrakeMinus] [real] NULL , '[脚制动力差]
[LBlock] [real] NULL , ' [左阻滞力]
[RBlock] [real] NULL , '[右阻滞力]
[fullUpspeed] [real] NULL , ' [满载门架起升速度]
[fulldownspeed] [real] NULL , ' [满载门架下降速度]
[UpMaxI] [real] NULL , '[起升电机最大电流]
[UpSteadyI] [real] NULL , '[起升电机稳态电流]
[TurnMaxI] [real] NULL , '[转向电机最大电流]
[TurnSteadyI] [real] NULL , '[转向电机稳态电流]
/*[P_workposition1]表的INSERT触发器*/
create trigger trg_insert_P_workposition1 on [P_workposition1]
for insert
as
----如果[report]表不存在当前叉车条码则插入
INSERT INTO [report]([forkNo],[forkType])
SELECT i.[叉车条形码],i.[叉车类型]
FROM inserted as i LEFT JOIN [report] as r
ON i.[叉车条形码] = r.[forkNo] and i.[叉车类型] = r.[forkType]
WHERE r.[forkNo] IS NULL and r.[forkType] IS NULL
----更新[report]表中当前叉车的[P_workposition1]测试内容
UPDATE r SET
[tester] = i.[测试员工号],
[forwardSpeed1] = i.[前进一档速度],
[revSpeed1] = i.[倒退一档速度],
[steadyI] = i.[行进电机稳态电流],
[batteryV] = i.[电池电压]
FROM [report] as r INNER JOIN inserted as i
ON i.[叉车条形码] = r.[forkNo] and i.[叉车类型] = r.[forkType]
GO
report表中同车号数据未有时没有问题,当report表中已有同车号数据时出现问题,违反了primary key 约束 'pk_report ',不能在对象 'report '中插入重复键.
当任一工位,车辆再次检测时,即p_workposition1,p_workposition2,p_workposition3中再次插入同车号记录时,触发器则再次往report中插入了记录.(应该不插入而仅进行update) 好象触发器中where子句未发生作用.
[解决办法]
楼主当时并没有说明report表的主键,所以另一个帖子回复时就把叉车号与叉车类型作为联合主键了.
请测试一下修改后的触发器:
/*[P_workposition1]表的INSERT触发器*/
create trigger trg_insert_P_workposition1 on [P_workposition1]
for insert
as
----如果[report]表不存在当前叉车条码则插入
INSERT INTO [report]([forkNo])
SELECT i.[叉车条形码]
FROM inserted as i LEFT JOIN [report] as r
ON i.[叉车条形码] = r.[forkNo]
WHERE r.[forkNo] IS NULL
----更新[report]表中当前叉车的[P_workposition1]测试内容
UPDATE r SET
[forkType] = i.[叉车类型],
[tester] = i.[测试员工号],
[forwardSpeed1] = i.[前进一档速度],
[revSpeed1] = i.[倒退一档速度],
[steadyI] = i.[行进电机稳态电流],
[batteryV] = i.[电池电压]
FROM [report] as r INNER JOIN inserted as i
ON i.[叉车条形码] = r.[forkNo]
GO
/*[P_workposition2]表的INSERT触发器*/
create trigger trg_insert_P_workposition2 on [P_workposition2]
for insert
as
----如果[report]表不存在当前叉车条码则插入
INSERT INTO [report]([forkNo])
SELECT i.[叉车条形码]
FROM inserted as i LEFT JOIN [report] as r
ON i.[叉车条形码] = r.[forkNo]
WHERE r.[forkNo] IS NULL
----更新[report]表中当前叉车的[P_workposition2]测试内容
UPDATE r SET
[forkType] = i.[叉车类型],
[tester] = i.[测试员工号],
[LHandBrake] = i.[手左制动力],
[RHandBrake] = i.[手右制动力],
[LFootBrake] = i.[脚左制动力],
[RFootBrake] = i.[脚右制动力],
[handBrakeSum] = i.[手制动力和],
[handBrakeMinus] = i.[手制动力差],
[footBrakeSum] = i.[脚制动力和],
[footBrakeMinus] = i.[脚制动力差],
[LBlock] = i.[左阻滞力],
[RBlock] = i.[右阻滞力]
FROM [report] as r INNER JOIN inserted as i
ON i.[叉车条形码] = r.[forkNo]
GO
/*[P_workposition3]表的INSERT触发器*/
create trigger trg_insert_P_workposition3 on [P_workposition3]
for insert
as
----如果[report]表不存在当前叉车条码则插入
INSERT INTO [report]([forkNo])
SELECT i.[叉车条形码]
FROM inserted as i LEFT JOIN [report] as r
ON i.[叉车条形码] = r.[forkNo]
WHERE r.[forkNo] IS NULL
----更新[report]表中当前叉车的[P_workposition3]测试内容
UPDATE r SET
[forkType] = i.[叉车类型],
[tester] = i.[测试员工号],
[fullUpspeed] = i.[满载门架起升速度],
[fulldownspeed] = i.[满载门架下降速度],
[UpMaxI] = i.[起升电机最大电流],
[UpSteadyI] = i.[起升电机稳态电流],
[TurnMaxI] = i.[转向电机最大电流],
[TurnSteadyI] = i.[转向电机稳态电流]
FROM [report] as r INNER JOIN inserted as i
ON i.[叉车条形码] = r.[forkNo]
GO
