各位老师: 关于工资扣税的问题,从触发器如何做.下面的代码不知错在那里.请帮帮我吧. 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;
解决方案 »
- 用XML保存了菜单层次关系,怎么读取后,动态生成菜单
- 使用IdFTP控件碰到的一些问题
- 请问哪里可以下载到delphi的参考手册呢?类似于msdn的那种,可以查函数,过程和控件的!谢谢~
- 新手恳求dephi入门经验~~~~~~~~~~~~~~~~~~
- 用DELPHI实现生成错误日志的功能
- delphi中能做主-子窗体的结构吗?如何实现?谢谢
- 一个关于报表的问题
- 如何将一个表名传入一个过程中?
- 怎么样拦截一般的出错信息,加上自己的出错信息呢?
- 在picture上放一个label,picture在下,label在上 ,移动picture,label也跟着移动
- sql中怎样取某个字段的后几位
- DELPHIz中如何用16进制数据查找出某个进程中相对应的地址
你的@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;