declare @条件id int,@temp1 int,@temp2 intselect @temp1=isnull(sum(sgs_percent),0) from tb_sgradestandard group by sgs_id where sgs_id=@条件idselect @temp2=sgs_percent from tb_gradestandard where sgs_id=@条件idif (@temp1>@temp2 or @temp>100)
select '错误!'
else
select 'OK!'
select '错误!'
else
select 'OK!'
declare @条件id int,@temp1 int,@temp2 int
-----小的评分标准
select @temp1=isnull(sum(sgs_percent),0) from tb_sgradestandard group by sgs_id where sgs_id=@条件id
-----大的评分标准
select @temp2=sgs_percent from tb_gradestandard where sgs_id=@条件id
-----比较
if (@temp1>@temp2 or @temp1>100)
select '错误!'
else
select 'OK!
declare @条件id int,@temp1 int,@temp2 int
@条件是什么?
case
when isnull(a.sgs_percent,0)>isnull(b.sgs_percent,0) or isnull(sum(sgs_percent),0)>100 then '错误'
else 'OK'
end 结果 into #结果表
from (select sgs_id,sum(sgs_percent) sgs_percent tb_sgradestandard group by sgs_id) a,tb_gradestandard b where a.sgs_id=b.sgs_idselect * from #结果表
select gs_id,
case
when isnull(a.sgs_percent,0)>isnull(b.sgs_percent,0) or isnull(sum(sgs_percent),0)>100 then '错误'
else 'OK'
end 结果 into #结果表
from (select sgs_id,sum(sgs_percent) sgs_percent tb_sgradestandard group by sgs_id) a,tb_gradestandard b where a.sgs_id=b.sgs_idselect * from #结果表
用触发器也行!值里没有负数,一次会插入、删除、修改多条记录。from (select sgs_id,sum(sgs_percent) sgs_percent tb_sgradestandard (运行时出错说里有错)group by sgs_id) a,tb_gradestandard b where a.sgs_id=b.sgs_id不有这a,b是什么呀?
我是想添加一条记录时就检验一下!
是的话就:
create procedure 过程名
@sgs_id int,
@sgs_standard char(20),
@sgs_percent sgs_percent float,
@gs_id int,
@sgs_memo char(100)
as
begin
declare @temp1 int,@temp2 int
-----小的评分标准有相同sgs_id所以要group by
select @temp1=isnull(sum(sgs_percent),0) from tb_sgradestandard group by sgs_id where sgs_id=@sgs_id
-----大的评分标准
select @temp2=sgs_percent from tb_gradestandard where sgs_id=@sgs_id
----判断符合要求就插入
if (@temp1>@temp2 or @temp1>100)
insert tb_sgradestandard values (@sgs_id,@sgs_standard,@sgs_percent,@gs_id,@sgs_memo)
end调用此存储过程
过程名 参数1,参数2,参数3,参数4,参数5
如:
过程名 1,'xxs','ff',3,'sdsdf'
条件: 一:控制sgs_percent的总值不超过100
二:控制当前输入的sgs_percent的总值不超过相应的在tb_gradestandard里 的等于gs_id的值的gs_percent值。也就是汇总所有tb_sgradestandard的gs_id值等于tb_gradestandard里的gs_id值时的sgs_percent的值!
create trigger tri_test on tb_sgradestandard
on insert,update
as
begin
declare @i int,@j int
declare @a int,@b int set @a=(select top 1 sum(b.sgs_percent) from inserted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) set @b=(select top 1 sum(b.gs_percent) from inserted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) if @a>@b or @a>100
roalback
end要控制,tb_gradestandard,也要做一个相应的触发器,不然它删除了,怎么办?
select @temp1=isnull(sum(sgs_percent),0) from tb_sgradestandard group by sgs_id where sgs_id=@sgs_id //这where条件是什么意思?我不懂耶!这个@sgs_id 是用户输入的参数吧?怎么要用这个sgs_id 值与用户输入的值进行比较!
select @temp1=isnull(sum(sgs_percent),0) from tb_sgradestandard where gs_id=@sgs_idif @temp>=100 就报错了!是不是这样???还是if 输入的sgs_percent>=100 就报错了!
create trigger tri_test2 on tb_gradestandard
on update,delete
as
begin
declare @i int
declare @a int,@b int
select @i=count(*) from inserted
if @i<>0
begin
set @a=(select top 1 sum(b.sgs_percent) from inserted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) set @b=(select top 1 sum(b.gs_percent) from inserted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent)
end
if @i=0
begin
set @a=(select top 1 sum(b.sgs_percent) from deleted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) set @b=(select top 1 sum(b.gs_percent) from deleted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent)
end
if isnull(@a,0)>isnull(@b,0) or isnull(@a,0)>100
roalback
end
运行你的在这里出错:where sgs_id=@sgs_id
declare @sgs_id
set @sgs_id=1
select @temp1=isnull(sum(sgs_percent),0) from tb_sgradestandard where gs_id=@sgs_idif @temp>=100
select '错了'
else
select '对了'
select * from tb_sgradestandard 别名1,tb_gradestandard 别名2 where tb_sgradestandard.gs_id=tb_gradestandard.gs_id
是吗??
set @a=(..........):create trigger tri_test on tb_sgradestandard
on insert,update
as
begin
declare @i int,@j int
declare @a int,@b int set @a=(select top 1 sum(b.sgs_percent) from inserted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) ) set @b=(select top 1 sum(b.gs_percent) from inserted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) ) if isnull(@a,0)>isnull(@b,0) or isnull(@a,0)>100
roalback
end
tb_gradestandard:
create trigger tri_test2 on tb_gradestandard
on update,delete
as
begin
declare @i int
declare @a int,@b int
select @i=count(*) from inserted
if @i<>0
begin
set @a=(select top 1 sum(b.sgs_percent) from inserted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) ) set @b=(select top 1 sum(b.gs_percent) from inserted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) )
end
if @i=0
begin
set @a=(select top 1 sum(b.sgs_percent) from deleted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) ) set @b=(select top 1 sum(b.gs_percent) from deleted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) )
end
if isnull(@a,0)>isnull(@b,0) or isnull(@a,0)>100
roalback
end
create trigger tri_test on tb_sgradestandard
on insert,update
as
begin
declare @i int,@j int
declare @a int,@b int set @a=(select top 1 sum(b.sgs_percent) from inserted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc) set @b=(select top 1 sum(b.gs_percent) from inserted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc) if isnull(@a,0)>isnull(@b,0) or isnull(@a,0)>100
roalback
end
tb_gradestandard:
create trigger tri_test2 on tb_gradestandard
on update,delete
as
begin
declare @i int
declare @a int,@b int
select @i=count(*) from inserted
if @i<>0
begin
set @a=(select top 1 sum(b.sgs_percent) from inserted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc) set @b=(select top 1 sum(b.gs_percent) from inserted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc)
end
if @i=0
begin
set @a=(select top 1 sum(b.sgs_percent) from deleted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc) set @b=(select top 1 sum(b.gs_percent) from deleted a,tb_gradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc)
end
if isnull(@a,0)>isnull(@b,0) or isnull(@a,0)>100
roalback
end
set @a=(select top 1 sum(b.sgs_percent) from inserted a,tb_sgradestandard b where a.gs_id=b.gs_id group by gs_id order by sum(b.sgs_percent) desc)//这里from inserted a,tb_sgradestandard b
是什么意思呀?
所有的:group by a.gs_id 改成:group by a.gs_id
inserted,deleted 是触发器内的表,具体看书或帮助
from inserted a,tb_sgradestandard b
别名:
inserted --a
tb_sgradestandard --b
昨天的问题搞定了吗????