sql server数据库表中有两个字段,一个rodNo一个machineNo,现在想添加一个判断约束,即为当rodNo相同时,machineNo必须也相同,不然不允许插入数据,比如:
rodNo machineNo
a01 b01
a01 b01
可以
但是
rodNo machineNo
a01 b01
a01 b01
a01 b02
就不可以了是否可以实现?
rodNo machineNo
a01 b01
a01 b01
可以
但是
rodNo machineNo
a01 b01
a01 b01
a01 b02
就不可以了是否可以实现?
instead of insert
as
begin
if exists(select 1 from inserted i
where exists(select 1 from tb where rodNo=i.rodNo and machineNo=i.machineNo))
begin
insert tb select * from inserted i
where exists(select 1 from tb where rodNo=i.rodNo and machineNo=i.machineNo)
end
end
ON tb
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (SELECT 1
FROM inserted i,
tb t
WHERE i.rodno = t.rodno
AND i.machineno <> t.machineno)
BEGIN
rasieerror ( '插入失败!插入数据不符合要求' ,16,1 )
END
END
ON tb
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (SELECT 1
FROM inserted i,
tb t
WHERE i.rodno = t.rodno
AND i.machineno <> t.machineno)
BEGIN
RAISERROR( '插入失败!插入数据不符合要求' ,16,1 )
END
END