一、表中有两字段
name type
张三 1
张三 4
张三 -1
如果增加：
张三 3
就不成功，即当(type=0 or type = 1 or type = 3)时不能重复增加?
二、编码字段
billno
BM0001-20141211
BM0002-20141212
要求substring(billno,3,4)不能重复，即不能增加BM0001-20141212
以上如何增加check约束实现
------解决思路----------------------
可用触发器实现方法
USE tempdb
go
CREATE TABLE TAB(billno VARCHAR(50) )
go
go
CREATE TRIGGER tr_cTAB ON TAB
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS(SELECT 1 FROM INSERTED AS i WHERE EXISTS(SELECT 1 FROM TAB WHERE SUBSTRING(billno,3,4)=SUBSTRING(i.billno,3,4)))
BEGIN
RAISERROR (N'bill重复',16,1)
RETURN
END
INSERT INTO TAB SELECT billno FROM INSERTED
END
GO
INSERT INTO TAB(billno) VALUES('BM0001-20141211')--OK
INSERT INTO TAB(billno) VALUES('BM0001-20141211')--Error
DROP TABLE TAB
------解决思路----------------------
问题1,用触发器实现.
create table gz1(name varchar(10),type int)
insert into gz1(name,type)
select '张三',1 union all
select '张三',4 union all
select '张三',-1
-- 创建触发器
create trigger tr_gz1 on gz1
for insert
as
begin
if exists(select 1
from gz1 a
inner join inserted b on a.name=b.name
where a.type in(0,1,3))
and exists(select 1
from inserted
where type in(0,1,3))
begin
raiserror(N'当(type=0 or type = 1 or type = 3)时不能重复增加.',16,1)
rollback tran
end
end
-- 测试1
insert into gz1(name,type) select '张三',3
/*
Msg 50000, Level 16, State 1, Procedure tr_gz1, Line 14
当(type=0 or type = 1 or type = 3)时不能重复增加.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
-- 测试2
insert into gz1(name,type) select '张三',5
/*
(1 row(s) affected)
*/
-- 测试3
insert into gz1(name,type) select '张三',0
/*
Msg 50000, Level 16, State 1, Procedure tr_gz1, Line 14
当(type=0 or type = 1 or type = 3)时不能重复增加.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
-- 结果
select * from gz1
/*
name type
---------- -----------
张三 1
张三 4
张三 -1
张三 5
(4 row(s) affected)
*/