请教这个触发器怎么写?
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 ,
[测试时间] [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 ,
[forkWeight] [real] NULL ,
[Testdegree] [int] NULL ,
[driveType] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Testdate] [datetime] NULL ,
[gearboxNo] [char] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[gearboxFac] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[stepmotorNo] [char] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[stepmotorFac] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[UpmotorNo] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[UpmotorFac] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[UpPumpNo] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[UpPumpFac] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[TurnmotorNo] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[TurnmotorFac] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[TurnPumpNo] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[TurnPumpFac] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
[解决办法]
/*[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
