触发器不熟练。id thins itemid
1 111 123
2 222 123
3 333 123
4 444 234
5 555 234
6 666 234
7 777 345
8 888 345
9 999 345. ... ...每次添加事触发判断itemid有重复时,只保留其对应ID最大那一条
id thins itemid
3 333 123
6 666 234
9 999 345
1 111 123
2 222 123
3 333 123
4 444 234
5 555 234
6 666 234
7 777 345
8 888 345
9 999 345. ... ...每次添加事触发判断itemid有重复时,只保留其对应ID最大那一条
id thins itemid
3 333 123
6 666 234
9 999 345
CREATE TRIGGER TRI_TB
ON TB
FOR INSERT
AS
BEGIN
DECLARE @ITEMID INT,@ID INT
SELECT @ITEMID=ITEMID FROM INSERTED
SELECT @ID=MAX(ID) FROM TB WHERE ITEMID=@ITEMID
DELETE TB WHERE ITEMID= @ITEMID AND ID <@ID
END
ON b_
FOR INSERT
AS
BEGIN
DELETE a FROM b_ a WHERE EXISTS(SELECT 1 FROM b_ WHERE itemid=a.itemid AND thins>a.thins)
END
CREATE TRIGGER Tri_tableA_Ins on dbo.tableA
FOR Insert
AS
declare @id int
declare @itemid int Select @id=max(a.id),@itemid=b.itemid
from tableA a,INSERTED b
where a.itemid=b.itemid Delete from tableA where id<>@id and itemid=@itemid
on tb
for insert
as
begin
delete tb a where exists(select 1 from tb items=a.itemid and id>a.id)
end
go
on tb
for insert
as
begin
delete tb a where exists(select 1 from inserted,tb where tb.items=inserted.itemid and tb.items=a.items and tb.id>a.id)
end
go