一张表里有6个列,如下 A | B | C | D | E | F A,B,C,D列中的任意一列数据与E,F组合不能有重复的数据,比如表中已有如下数据 A | B | C | D | E | F 1 0 0 0 7 8 0 2 3 4 11 12 那么,如果A:1,E:7 D:0,F:8 B:2,E:11 C:3,F:12 这样的数据不能插入,也就是表中不能有这样的数据存在. 请各位高手帮忙解答,应当建什么样的索引或者用什么方法能够实现?
create trigger trg_b
on b
INSTEAD OF insert
as
declare @ae int, @af int
declare @be int, @bf int
declare @ce int ,@cf int
declare @de int, @df int
declare @exist int
select @ae=checksum(a,e),@af=checksum(a,f),
@be=checksum(b,e),@bf=checksum(b,f),
@ce=checksum(c,e),@cf=checksum(c,f),
@de=checksum(d,e),@df=checksum(d,f)
from inserted
select @exist =count(1) from [table] where checksum(a,e)=@ae or checksum(b,e)=@be or checksum(c,e)=@ce or checksum(d,e)=@de or checksum(a,f)=@af or
checksum(b,f)=@bf or checksum(c,f)=@cf or checksum(d,f)=@df
if(@exist>0)
raiserror('数据已经存在!',16,1)
else begin
insert into [table] select a,b,c,d,e,f from inserted
end
create trigger tr_test on testtable
for insert
as
if exists
(
select 1
from testtable a,inserted b
where
(
a.A=b.A or
a.B=b.B or
a.C=b.C or
a.D=b.D
)
and
(
a.E=b.E or
a.F=b.F
)
)
rollback transaction