CREATE TRIGGER t ON ta FOR UPDATE,INSERT AS BEGIN TRUNCATE TABLE tb INSERT tb SELECT a.id,a.deviceName,a.paramName,a.value,a.time FROM ta a INNER JOIN (SELECT MAX(time) mt,deviceName,paramName FROM ta GROUP BY deviceName,paramName) b ON a.deviceName=b.deviceName AND a.paramName=b.paramName AND a.time=b.timte END 当然,写法还有很多种,不一一列了。
CREATE TRIGGER t ON ta FOR UPDATE,INSERT AS BEGIN -- 如果表b有对应deviceName,paramName的记录,则更新 UPDATE a SET VALUE=b.VALUE,TIME=b.TIME FROM 表b a INNER JOIN inserted b ON a.deviceName=b.deviceName AND a.paramName=b.paramName -- 否则插入新记录 INSERT 表b SELECT a.id,a.deviceName,a.paramName,a.value,a.time FROM inserted a LEFT JOIN 表b b ON a.deviceName=b.deviceName AND a.paramName=b.paramName WHERE b.deviceName IS NULL END
谢谢各位:我想先删除一条,然后再插入,下面写法可以吗?还有就是那种执行速度快 CREATE TRIGGER t ON ta FOR INSERT AS BEGIN delete from ta, inserted where ta.deviceName = inserted.deviceName and ta.paramName = inserted.paramName insert ta select id,deviceName,paramName,value,time from inserted END
--> --> (Andy)生成测试数据 2008-07-20 Set Nocount On if not object_id('TestA') is null drop table TestA Go Create table TestA([ID] int Identity(1,1) Primary Key,[DeviceName] nvarchar(3),[ParamName] nvarchar(6),[Value] int,[time] Datetime) Go if not object_id('TestB') is null drop table TestB Go Create table TestB([ID] int Identity(1,1) Primary Key,[DeviceName] nvarchar(3),[ParamName] nvarchar(6),[Value] int,[time] Datetime) Go If Object_id('t_testA') Is Not null Drop trigger t_testA Go Create Trigger t_testA On TestA For Insert,Update As If Object_id('tempdb..#') Is Not null Drop Table # Select * Into # From Inserted If Exists(Select 1 From TestB a Inner Join # b On b.DeviceName=a.DeviceName And b.ParamName=a.ParamName) Update a Set a.[Value]=b.[Value],a.[time]=b.[time] From TestB a Inner Join # b On b.DeviceName=a.DeviceName And b.ParamName=a.ParamName Else Insert Into TestB (DeviceName,ParamName,[Value],[time]) Select DeviceName,ParamName,[Value],[time] From #
Go Insert TestA select N'ACU',N'param1',1,'2008-07-19 14:59:44' Insert TestA select N'ACU',N'param1',2,'2008-07-19 15:00:44' Insert TestA select N'ACU',N'param1',3,'2008-07-19 15:20:39' Insert TestA select N'ACU',N'param2',1,'2008-07-19 14:59:44' Insert TestA select N'ACU',N'param2',2,'2008-07-19 15:00:44' Insert TestA select N'ACU',N'param2',2,'2008-07-19 15:20:39' Go Select * From TestB /* ID DeviceName ParamName Value time ----------- ---------- --------- ----------- ----------------------- 1 ACU param1 3 2008-07-19 15:20:39.000 2 ACU param2 2 2008-07-19 15:20:39.000 */
你的意思是在这两个表里面查找最新的记录,然后将它们添加到表B之中。
FOR UPDATE,INSERT
AS
BEGIN
TRUNCATE TABLE tb
INSERT tb SELECT a.id,a.deviceName,a.paramName,a.value,a.time
FROM ta a
INNER JOIN
(SELECT MAX(time) mt,deviceName,paramName FROM ta GROUP BY deviceName,paramName) b
ON a.deviceName=b.deviceName AND a.paramName=b.paramName AND a.time=b.timte
END
当然,写法还有很多种,不一一列了。
CREATE TRIGGER t ON ta
FOR UPDATE,INSERT
AS
BEGIN -- 如果表b有对应deviceName,paramName的记录,则更新
UPDATE a SET VALUE=b.VALUE,TIME=b.TIME
FROM 表b a
INNER JOIN inserted b ON a.deviceName=b.deviceName AND a.paramName=b.paramName -- 否则插入新记录
INSERT 表b
SELECT a.id,a.deviceName,a.paramName,a.value,a.time
FROM inserted a
LEFT JOIN 表b b ON a.deviceName=b.deviceName AND a.paramName=b.paramName
WHERE b.deviceName IS NULL
END
CREATE TRIGGER t ON ta
FOR INSERT
AS
BEGIN
delete from ta, inserted where ta.deviceName = inserted.deviceName and ta.paramName = inserted.paramName
insert ta select id,deviceName,paramName,value,time from inserted
END
Set Nocount On
if not object_id('TestA') is null
drop table TestA
Go
Create table TestA([ID] int Identity(1,1) Primary Key,[DeviceName] nvarchar(3),[ParamName] nvarchar(6),[Value] int,[time] Datetime)
Go
if not object_id('TestB') is null
drop table TestB
Go
Create table TestB([ID] int Identity(1,1) Primary Key,[DeviceName] nvarchar(3),[ParamName] nvarchar(6),[Value] int,[time] Datetime)
Go
If Object_id('t_testA') Is Not null
Drop trigger t_testA
Go
Create Trigger t_testA On TestA
For Insert,Update
As
If Object_id('tempdb..#') Is Not null
Drop Table #
Select * Into # From Inserted
If Exists(Select 1 From TestB a Inner Join # b On b.DeviceName=a.DeviceName And b.ParamName=a.ParamName)
Update a Set a.[Value]=b.[Value],a.[time]=b.[time]
From TestB a
Inner Join # b On b.DeviceName=a.DeviceName And b.ParamName=a.ParamName
Else
Insert Into TestB (DeviceName,ParamName,[Value],[time])
Select DeviceName,ParamName,[Value],[time] From #
Go
Insert TestA select N'ACU',N'param1',1,'2008-07-19 14:59:44'
Insert TestA select N'ACU',N'param1',2,'2008-07-19 15:00:44'
Insert TestA select N'ACU',N'param1',3,'2008-07-19 15:20:39'
Insert TestA select N'ACU',N'param2',1,'2008-07-19 14:59:44'
Insert TestA select N'ACU',N'param2',2,'2008-07-19 15:00:44'
Insert TestA select N'ACU',N'param2',2,'2008-07-19 15:20:39'
Go
Select * From TestB
/*
ID DeviceName ParamName Value time
----------- ---------- --------- ----------- -----------------------
1 ACU param1 3 2008-07-19 15:20:39.000
2 ACU param2 2 2008-07-19 15:20:39.000
*/