CREATE TRIGGER 名2 ON dbo.tabMain FOR update AS if exists(select 1 from inserted where exists(select 1 from 表1 where 题号=inserted.题号 and inserted.分数 not between 最小值 and 最大值)) rollback tran
用触发器来实现就行了:--下面的触发器实现,在插入或更新时,如果某条记录不满足要求,则取消插入/修改 create trigger t_chk on 表2 for insert,update if exists(select 1 from inserted a inner join 表1 b on a.题号=b.题号 where a.分数<b.最小值 or a.分数>b.最大值) rollback tran go
CREATE TRIGGER 名2 ON 表2 FOR update AS if exists(select 1 from inserted where exists(select 1 from 表1 where 题号=inserted.题号 and inserted.分数 not between 最小值 and 最大值)) rollback tran
谢谢pengdali(大力 V3.0) 和zjcxc(邹建) !
create table 表1 ( 题号 int, 最大值 int, 最小值 int ) goinsert into 表1 values(1,10,0) insert into 表1 values(2,5,0) insert into 表1 values(3,3,1) gocreate table 表2 ( 卷号 int, 题号 int, 分数 int ) goinsert into 表2 values(1,1,0) insert into 表2 values(1,2,0) insert into 表2 values(1,3,0) insert into 表2 values(2,1,0) insert into 表2 values(2,2,0) insert into 表2 values(2,3,0) goupdate 表2 set 分数=(select case when 7<最大值 and 7>最小值 then 7 else 0 end from 表1 where 表1.题号=表2.题号) 1 1 7 1 2 0 1 3 0 2 1 7 2 2 0 2 3 0
FOR update
AS
if exists(select 1 from inserted where exists(select 1 from 表1 where 题号=inserted.题号 and inserted.分数 not between 最小值 and 最大值))
rollback tran
create trigger t_chk on 表2
for insert,update
if exists(select 1 from inserted a inner join 表1 b on a.题号=b.题号 where a.分数<b.最小值 or a.分数>b.最大值)
rollback tran
go
FOR update
AS
if exists(select 1 from inserted where exists(select 1 from 表1 where 题号=inserted.题号 and inserted.分数 not between 最小值 and 最大值))
rollback tran
(
题号 int,
最大值 int,
最小值 int
)
goinsert into 表1 values(1,10,0)
insert into 表1 values(2,5,0)
insert into 表1 values(3,3,1)
gocreate table 表2
(
卷号 int,
题号 int,
分数 int
)
goinsert into 表2 values(1,1,0)
insert into 表2 values(1,2,0)
insert into 表2 values(1,3,0)
insert into 表2 values(2,1,0)
insert into 表2 values(2,2,0)
insert into 表2 values(2,3,0)
goupdate 表2 set 分数=(select case when 7<最大值 and 7>最小值 then 7 else 0 end from 表1 where 表1.题号=表2.题号)
1 1 7
1 2 0
1 3 0
2 1 7
2 2 0
2 3 0