各位老师: 关于工资扣税的问题,从触发器如何做.下面的代码不知错在那里.请帮帮我吧. sql server 2000 中一个表,字段有
编号,姓名,基本工资,工龄工资,奖金,税金,想通过触发器当修改基本工资等项数据就改变税金的数 CREATE TRIGGER up ON [dbo].[gzbsj]
FOR UPDATE
AS
declare @a float;
declare @b float;
declare @c float;
declare @tax float;
declare @je float; begin
select @tax=0;
select @a=基本工资 from inserted;
select @b=工龄工资 from inserted;
select @c=奖金 from inserted;
select @je=@a+@b+@c-2000
if (@je>20000)
select @tax=@tax+(@je-20000)*0.25;
select @je=20000;
end if if (@je>5000)
select @tax=@tax+(@je-5000)*0.20;
select @je=5000;
end if if (@je>2000)
select @tax=@tax+(@je-2000);
select @je=2000;
end if if (@je>500)
select @tax=@tax+(@je-500)*0.10;
select @je=500;
end if if (@je>0)
select @tax=@tax+@je*0.05;
end if
update gzbsj set 税金=@tax where 编号=(select 编号 from inserted);
end;
编号,姓名,基本工资,工龄工资,奖金,税金,想通过触发器当修改基本工资等项数据就改变税金的数 CREATE TRIGGER up ON [dbo].[gzbsj]
FOR UPDATE
AS
declare @a float;
declare @b float;
declare @c float;
declare @tax float;
declare @je float; begin
select @tax=0;
select @a=基本工资 from inserted;
select @b=工龄工资 from inserted;
select @c=奖金 from inserted;
select @je=@a+@b+@c-2000
if (@je>20000)
select @tax=@tax+(@je-20000)*0.25;
select @je=20000;
end if if (@je>5000)
select @tax=@tax+(@je-5000)*0.20;
select @je=5000;
end if if (@je>2000)
select @tax=@tax+(@je-2000);
select @je=2000;
end if if (@je>500)
select @tax=@tax+(@je-500)*0.10;
select @je=500;
end if if (@je>0)
select @tax=@tax+@je*0.05;
end if
update gzbsj set 税金=@tax where 编号=(select 编号 from inserted);
end;
你的@tax的结果肯定是 select @tax=@tax+@je*0.05;
前面的IF条件都是无用功
FOR UPDATE
AS
declare @a float;
declare @b float;
declare @c float;
declare @tax float;
declare @je float; begin
select @tax=0;
select @a=基本工资 from inserted;
select @b=工龄工资 from inserted;
select @c=奖金 from inserted;
select @je=@a+@b+@c-2000
if (@je>20000) begin
select @tax=@tax+(@je-20000)*0.25;
select @je=20000;
end if (@je>5000) begin
select @tax=@tax+(@je-5000)*0.20;
select @je=5000;
end if (@je>2000) begin
select @tax=@tax+(@je-2000);
select @je=2000;
end if (@je>500) begin
select @tax=@tax+(@je-500)*0.10;
select @je=500;
end if (@je>0) select @tax=@tax+@je*0.05; update gzbsj set 税金=@tax where 编号=(select 编号 from inserted); end;
FOR UPDATE
AS
begin
declare @a float;
declare @b float;
declare @c float;
declare @tax float;
declare @je float;
declare @tmpJe float; select @tax=0;
select @a=基本工资 from inserted;
select @b=工龄工资 from inserted;
select @c=奖金 from inserted;
select @je=@a+@b+@c-2000
if (@je>20000) begin
select @tax=@tax+(@je-20000)*0.25;
select @tmpJe =20000;
end;
if (@je>5000) and (@je <= 20000 ) begin
select @tax=@tax+(@je-5000)*0.20;
select @tmpJe =5000;
end ;
if (@je>2000) and (@je <= 5000) begin
select @tax=@tax+(@je-2000);
select @tmpJe =2000;
end;
if (@je>500) and (@je <= 2000) begin
select @tax=@tax+(@je-500)*0.10;
select @tmpJe =500;
end select @je = @tmpJe; if (@je>0) select @tax=@tax+@je*0.05; update gzbsj set 税金=@tax where 编号=(select 编号 from inserted); end; 應該是這樣吧。呵呵
select @tax=@tax+@je*0.05;
end if (@je>0)这个条件放到了最后,一般情况下是肯定成立的,所以,不管前面如何赋值给@tax,最后都会执行@tax=@tax+@je*0.05;
@tax只有一个值
试想若@je=21000,则所有的if语句都运行,显然是不对的。if语句部分应做如下修改; if (@je>20000)
begin
select @tax=@tax+(@je-20000)*0.25;
select @tmpJe =20000;
end
else
if (@je>5000) and (@je <= 20000 )
begin
select @tax=@tax+(@je-5000)*0.20;
select @tmpJe =5000;
end
else
if (@je>2000) and (@je <= 5000)
begin
select @tax=@tax+(@je-2000);
select @tmpJe =2000;
end
else
if (@je>500) and (@je <= 2000)
begin
select @tax=@tax+(@je-500)*0.10;
select @tmpJe =500;
end
else
select @tmpJe =0;