SQL2000:现有表AID NAME CODE TYPE
-------------------
1 A 01 1
2 B 02 1
3 C 03 1
4 D 04 1如果我在添加一条数据NAME=A, CODE=01, TYPE=1由于CODE, TYPE在同一条记录中已经出现过一次因此应该被禁止操作请问这样的触发器应该怎么写,即每次插入数据前根据指定的字段检查数据唯一性?
-------------------
1 A 01 1
2 B 02 1
3 C 03 1
4 D 04 1如果我在添加一条数据NAME=A, CODE=01, TYPE=1由于CODE, TYPE在同一条记录中已经出现过一次因此应该被禁止操作请问这样的触发器应该怎么写,即每次插入数据前根据指定的字段检查数据唯一性?
ALTER TABLE TA
ADD CONSTRAINT UNIQUE_NAME UNIQUE(CODE,TYPE)
create table taoistong
(id int identity(1,1)
,name char(1)
,code char(2)
,type char(1))
insert into taoistong select
'A', '01', 1
insert into taoistong select
'B', '02', 1
insert into taoistong select
'C', '03', 1
insert into taoistong select
'D', '04', 1 alter trigger tr_i_taoistong on taoistong
instead of Insert
as
insert into Taoistong
select name,code,type
from inserted a
where not exists(select 1 from Taoistong b where b.name=a.name and b.code=a.code and b.type=a.type)insert into taoistong select
'D', '04', 1
insert into taoistong select
'E', '04', 1
可以解决
删除约束 : alter table tb drop constraint 约束名
drop table ggcreate table gg
(id int identity(1,1)
,name char(1)
,code char(2)
,type char(1))
insert into gg
select 'A', '01', 1
union
select 'B', '02', 1
union
select 'C', '03', 1
union
select 'D', '04', 1 create trigger tr_gg on gg
instead of Insert
as
SET NOCOUNT ON
declare @msg char(30)
set @msg ='数据插入成功'
insert into gg
select name,code,type
from inserted a
where not exists(select 1 from gg b where b.name=a.name and b.code=a.code and b.type=a.type)
if @@rowcount>0
print @msg
else
raiserror('数据已经存在',16,10)
go
ALTER TABLE A
ADD CONSTRAINT UNIQUE_NAME UNIQUE(CODE,TYPE)
sp_helpconstraint tablename
找到该表的约束,然后用
alter table tb drop constraint 约束名
删除你不需要的约束。
ADD CONSTRAINT BM_ND_DrawingModel_check UNIQUE(DrawingTypeCode, VesselTypeID, ParentID)
GO
但显示不成功:Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is '239'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.请问这是怎么回事??我试过将ID主键取消,但仍然返回上述错误提示。