如何在插入一定记录数后,防止重复插入记录
比如
T1
id1 name1 cid
1 aa 1
2 bb 1
3 cc 2
如上述表T1
要求每个cid所对应的记录数为2条,
例如:
若再一次插入cid为1的记录就不执行inster或报错。
若再一次插入cid为2的记录就继续执行inster操作。
[解决办法]
if object_id('t1') is not null drop table t1 go create table t1([id1] int,[name1] varchar(10),[cid] int)insert t1 select 1,'aa',1union all select 2,'bb',1union all select 3,'cc',2goif object_id('tg') is not null drop trigger tg go CREATE trigger tg ON t1 for insert,update as if exists(select 1 from inserted t where cid in( select cid from t1 group by cid having count(cid)>2) )begin raiserror('您输入的CID不允许超过两条记录',16,1) rollbackend go -->开始测试 insert t1 select 4,'dd',1/*消息 50000,级别 16,状态 1,过程 tg,第 13 行您输入的CID不允许超过两条记录消息 3609,级别 16,状态 1,第 1 行事务在触发器中结束。批处理已中止。*/insert t1 select 4,'dd',2/*(1 行受影响)*/select * from t1/*id1 name1 cid----------- ---------- -----------1 aa 12 bb 13 cc 24 dd 2(4 行受影响)*/
[解决办法]
随手写了一下,你试试
-------------------------------------- Author: happyflystone -- Date : 2009-04-20 22:29:58-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) -- Apr 14 2006 01:12:25 -- Copyright (c) 1988-2005 Microsoft Corporation-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)-- -------------------------------------- Test Data: T1IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1GoCREATE TABLE T1(id1 INT,name1 NVARCHAR(2),cid INT)GoINSERT INTO T1SELECT 1,'aa',1 UNION ALLSELECT 2,'bb',1 UNION ALLSELECT 3,'cc',2 UNION ALLSELECT 4,'dd',3 UNION ALLSELECT 5,'ee',3 UNION ALL