日期:2014-05-17 浏览次数:20594 次
CREATE TABLE [dbo].[tab](
[id] [int] IDENTITY(1,1) NOT NULL,
[title] [nvarchar](50) NULL,
[name] [nvarchar](20) NULL,
[time] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TRIGGER tr_instead ON dbo.tab
INSTEAD OF INSERT
as
begin
--在插入数据时,检查最新的100条数据里、有多少条重复记录(按title,name两个字段检查重复);如果重复记录超过3条,就不再插入数据。
SELECT * FROM INSERTED
INSERT INTO tab(title, name, [time])
SELECT I.[title],I.[name], GETDATE()
FROM INSERTED I
left JOIN
(
SELECT TOP(100) title, name FROM tab WITH(NOLOCK) ORDER BY [time] DESC
) T
ON i.title = i.title
AND I.NAME = T.NAME
GROUP BY i.title, i.name
HAVING COUNT(t.title) < 3
END
GO
INSERT INTO [tab](TITLE,NAME, TIME)
SELECT '1', '1', GETDATE() --插入3次后,将插入不进去
/*
id title name time
1 1 1 2013-08-20 16:51:20.387
2 1 1 2013-08-20 16:51:25.150
3 1 1 2013-08-20 16:51:34.037
*/