求一触发器

[wzllin]

三个字段，id，bh,mc 其中id 是自增字段
求一触发器，bh 加 mc 存在的记录不允许插入

[wzllin]

引用 3 楼 sc273607742 的回复:

约束的命令不太熟，给你写个触发器的吧...

create trigger trigger_a
on a after insert  --假定你的表名叫a
as
begin
declare @id varchar(max)
declare @bh varchar(max)
declare @mc varchar(max)
select @id=convert(varchar,id),@bh=convert(varchar,bh),@mc=convert(varchar,mc) from inserted
if(select 1 from a where convert(varchar,bh)=@bh and convert(varchar,mc)=@mc) is not null
delete a where convert(varchar,id)=@id and convert(varchar,bh)=@bh and convert(varchar,mc)=@mc
end

没有重复的也添加不进去了

[哥眼神纯洁不]

引用 5 楼 wzllin 的回复:

[quote=引用 3 楼 sc273607742 的回复:] 约束的命令不太熟，给你写个触发器的吧...

create trigger trigger_a
on a after insert  --假定你的表名叫a
as
begin
declare @id varchar(max)
declare @bh varchar(max)
declare @mc varchar(max)
select @id=convert(varchar,id),@bh=convert(varchar,bh),@mc=convert(varchar,mc) from inserted
if(select 1 from a where convert(varchar,bh)=@bh and convert(varchar,mc)=@mc) is not null
delete a where convert(varchar,id)=@id and convert(varchar,bh)=@bh and convert(varchar,mc)=@mc
end

没有重复的也添加不进去了[/quote] 不好意思，写错了 create trigger trigger_a on a after insert --假定你的表名叫a as begin declare @id varchar(max) declare @bh varchar(max) declare @mc varchar(max) select @id=convert(varchar,id),@bh=convert(varchar,bh),@mc=convert(varchar,mc) from inserted if(select 1 from a where convert(varchar,bh)=@bh and convert(varchar,mc)=@mc) > 0 delete a where convert(varchar,id)=@id and convert(varchar,bh)=@bh and convert(varchar,mc)=@mc end 最好用约束控制，看4楼大神的

[哥眼神纯洁不]

约束也行吧...

[wzllin]

引用 楼主 wzllin 的回复:

三个字段，id，bh,mc 其中id 是自增字段 求一触发器，bh 加 mc 存在的记录不允许插入

求

[哥眼神纯洁不]

约束的命令不太熟，给你写个触发器的吧...

create trigger trigger_a
on a after insert  --假定你的表名叫a
as
begin
declare @id varchar(max)
declare @bh varchar(max)
declare @mc varchar(max)
select @id=convert(varchar,id),@bh=convert(varchar,bh),@mc=convert(varchar,mc) from inserted
if(select 1 from a where convert(varchar,bh)=@bh and convert(varchar,mc)=@mc) is not null
delete a where convert(varchar,id)=@id and convert(varchar,bh)=@bh and convert(varchar,mc)=@mc
end

[唐诗三百首]

用唯一约束即可..

 alter table [表名] add constraint uq_[表名] unique(bh,mc)