各位大侠帮看看,触发器问题

[dx_cyb]

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子句未发生作用.

[dx_cyb]

report表中仅保存最后一次的检测数据,主键为车号.

[dx_cyb]

把p_workposition1,p_workposition2,p_workposition3,report都清空了,问题没了.

[hellowork]

report表中仅保存最后一次的检测数据,主键为车号.
----------------------------------------------------------
如果主键只是车号而与叉车类型无关的话,请把上面的三个触发器中:
1.insert 语句中的"and r.[forkType] IS NULL" 去掉
2.update 语句中的 and i.[叉车类型] = r.[forkType] 去掉.

测试:
if object_id('tbStation1') is not null
drop table tbStation1
if object_id('tbStation2') is not null
drop table tbStation2
if object_id('tbReport') is not null
drop table tbReport
GO
create table tbStation1(forkno int,dt datetime)
create table tbStation2(forkno int,names varchar(20))
create table tbReport(forkno int primary key,dt datetime,names varchar(20))
GO
create trigger trg_insert_tbStation1 on tbStation1
for insert
as
----若不存在则插入
insert into tbReport(forkno)
select i.forkno from inserted as i
left join tbReport as r on i.forkno = r.forkno
where r.forkno is null
----更新
update r set forkno = i.forkno,dt = i.dt from tbReport as r
inner join inserted as i on r.forkno = i.forkno
GO
create trigger trg_insert_tbStation2 on tbStation2
for insert
as
----若不存在则插入
insert into tbReport(forkno)
select i.forkno from inserted as i
left join tbReport as r on i.forkno = r.forkno
where r.forkno is null
----更新
update r set forkno = i.forkno,names = i.names from tbReport as r
inner join inserted as i on r.forkno = i.forkno
GO
----插入测试数据以触发触发器
insert tbStation1 select 1,getdate()
insert tbStation1 select 2,getdate()
insert tbStation2 select 1,'Hello'
GO
----查看触发器效果
select 'Station1',* from tbStation1
select 'Station2',* from tbStation2
select 'Report',* from tbReport

----清除测试环境
DROP TABLE tbStation1,tbStation2,tbReport

/*结果
forkno dt
----------------------------------------------
Station1 1 2007-06-19 09:52:26.480
Station1 2 2007-06-19 09:52:26.490

forkno names
---------------------------------------
Station2 1 Hello

forkno dt names
--------------------------------------------------------
Report 1 2007-06-19 09:52:26.480 Hello
Report 2 2007-06-19 09:52:26.490 NULL
*/

[hellowork]

楼主当时并没有说明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