在我的TABLE里有一个deleteflag
唯一值的情况是COLUMN1+COLUMN2+(deleteflag=0)
也就是说
表中有数据
Column1 column2 deleteFalg
a b 1
a b 1
a b 0只有COLUMN1+COLUMN2+(deleteflag=0)
我要建这样的unique index可行吗?
或许只能CONSTRAINT
那这样的CONSTRAINT EXPESSION怎么写呢?
唯一值的情况是COLUMN1+COLUMN2+(deleteflag=0)
也就是说
表中有数据
Column1 column2 deleteFalg
a b 1
a b 1
a b 0只有COLUMN1+COLUMN2+(deleteflag=0)
我要建这样的unique index可行吗?
或许只能CONSTRAINT
那这样的CONSTRAINT EXPESSION怎么写呢?
CREATE TRIGGER [chk_data] ON [dbo].[Table2]
FOR INSERT, UPDATE
AS
if update(deleteFalg)
begin
if exists(select * from inserted where deleteFalg = 0)
begin
if exists(select * from Table2
join inserted on Table2.Column1 = inserted.Column1
and Table2.Column2 = inserted.Column2
and Table2.deleteFalg = inserted.deleteFalg
where Table2.id <> inserted.id and inserted.deleteFalg = 0)
begin
print '写入数据错误!'
rollback transaction
end
end
end上边的代码假定你的表名是Table2,id是这个表的主键,另外三列和楼主定义的一样。
CREATE TRIGGER [chk_data] ON [dbo].[Table2]
FOR INSERT, UPDATE
AS
if update(deleteFalg) or update(Column1) or update(Column2)
begin
if exists(select * from inserted where deleteFalg = 0)
begin
if exists(select * from Table2
join inserted on Table2.Column1 = inserted.Column1
and Table2.Column2 = inserted.Column2
and Table2.deleteFalg = inserted.deleteFalg
where Table2.id <> inserted.id and inserted.deleteFalg = 0)
begin
print '写入数据错误!'
rollback transaction
end
end
end
其实TRIG真的不是好东西
有谁知道这个约束该怎么写吗?
多谢
我去试试