if not exists (select 1 from tab where userid=@userid) begin insert ....... end
if object_id('[Table]') is not null drop table [Table] create table [Table]([UserID] int,[Name] varchar(20))declare @UserID int set @UserID=5 insert into [Table] select @UserID,'asdfas' where @userID not in(select UserID from [Table])drop table [Table]
create trigger ko on [table] inserted of insert as begin insert [table] select * from inserted i where not exists(select * from [table] where i.userid=userid) end
--第二种方法 就是 建立UNIQUE约束 if object_id('[Table]') is not null drop table [Table] go create table [Table]([UserID] int,[Name] varchar(20)) insert [Table] select 1,'a' union all select 2,'b' alter table [table] add constraint UN_USERID unique (userid)insert [Table] select 1,'a' /* 消息 2627,级别 14,状态 1,第 1 行 违反了 UNIQUE KEY 约束 'UN_USERID'。不能在对象 'dbo.Table' 中插入重复键。 语句已终止。*/
建立一个唯一约束: alter table [table] add constraint UN_USERID unique (userid)
insert into [Table] select @UserID,'asdfas' where @userID not in(select UserID from [Table])这条语句就可以实现啊!
一条SQL语句不行,可以在 userid 建唯一索引进行约束。
insert into [Table] select @UserID,'asdfas' where not exists(select * from [Table] where userid=@UserID) 试试这个?
declare @t table(userid int,username varchar(10))insert @t select 1,'aaaaaaaa' union all select 2,'bbbbb' union all select 3,'fffff' select * from @tdeclare @a int,@b intset @a=2 set @b=5--该插入不会成功 insert @t select @a,'插入A' where not exists(select * from @t where userid=@a)--该插入成功 insert @t select @b,'插入B' where not exists(select * from @t where userid=@b)select * from @t
begin
insert .......
end
create table [Table]([UserID] int,[Name] varchar(20))declare @UserID int
set @UserID=5
insert into [Table] select @UserID,'asdfas' where @userID not in(select UserID from [Table])drop table [Table]
inserted of insert
as
begin
insert [table]
select *
from inserted i
where not exists(select * from [table] where i.userid=userid)
end
if object_id('[Table]') is not null
drop table [Table]
go
create table [Table]([UserID] int,[Name] varchar(20))
insert [Table]
select 1,'a' union all
select 2,'b'
alter table [table] add constraint UN_USERID unique (userid)insert [Table]
select 1,'a'
/*
消息 2627,级别 14,状态 1,第 1 行
违反了 UNIQUE KEY 约束 'UN_USERID'。不能在对象 'dbo.Table' 中插入重复键。
语句已终止。*/
那你建个约束阿 是一条阿
alter table [table] add constraint UN_USERID unique (userid)看五楼阿
alter table [table] add constraint UN_USERID unique (userid)
试试这个?
declare @t table(userid int,username varchar(10))insert @t
select 1,'aaaaaaaa' union all
select 2,'bbbbb' union all
select 3,'fffff'
select *
from @tdeclare @a int,@b intset @a=2
set @b=5--该插入不会成功
insert @t
select @a,'插入A'
where not exists(select * from @t where userid=@a)--该插入成功
insert @t
select @b,'插入B'
where not exists(select * from @t where userid=@b)select *
from @t