create table wage
(employeeID int not null,
name varchar(10) not null,
wage money not null,
putdate datetime null
)
create table tax
(employeeID int not null,
name varchar(10) not null,
tax money not null,
paydate datetime null
)
go
create trigger cx on wage
for insert
as
if (select wage from inserted)>=1000 and (select wage from inserted)<=5000
begin
insert tax
select employeeID,name,wage*0.01,putdate from inserted
end
else if
(select wage from inserted)>5000
begin
insert tax
select employeeID,name,wage*0.03,putdate from inserted
end
goinsert wage
select 1,'x',6000,'2005-11-1'insert wage
select 2,'y',900,'2005-11-1'insert wage
select 3,'z',1500,'2005-11-1'
select * from wage
select * from taxemployeeID name wage putdate
----------- ---------- --------------------- ------------------------------------------------------
1 x 6000.0000 2005-11-01 00:00:00.000
2 y 900.0000 2005-11-01 00:00:00.000
3 z 1500.0000 2005-11-01 00:00:00.000(所影响的行数为 3 行)employeeID name tax paydate
----------- ---------- --------------------- ------------------------------------------------------
1 x 180.0000 2005-11-01 00:00:00.000
3 z 15.0000 2005-11-01 00:00:00.000(所影响的行数为 2 行)
(employeeID int not null,
name varchar(10) not null,
wage money not null,
putdate datetime null
)
create table tax
(employeeID int not null,
name varchar(10) not null,
tax money not null,
paydate datetime null
)
go
create trigger cx on wage
for insert
as
if (select wage from inserted)>=1000 and (select wage from inserted)<=5000
begin
insert tax
select employeeID,name,wage*0.01,putdate from inserted
end
else if
(select wage from inserted)>5000
begin
insert tax
select employeeID,name,wage*0.03,putdate from inserted
end
goinsert wage
select 1,'x',6000,'2005-11-1'insert wage
select 2,'y',900,'2005-11-1'insert wage
select 3,'z',1500,'2005-11-1'
select * from wage
select * from taxemployeeID name wage putdate
----------- ---------- --------------------- ------------------------------------------------------
1 x 6000.0000 2005-11-01 00:00:00.000
2 y 900.0000 2005-11-01 00:00:00.000
3 z 1500.0000 2005-11-01 00:00:00.000(所影响的行数为 3 行)employeeID name tax paydate
----------- ---------- --------------------- ------------------------------------------------------
1 x 180.0000 2005-11-01 00:00:00.000
3 z 15.0000 2005-11-01 00:00:00.000(所影响的行数为 2 行)
(employeeID int not null,
name varchar(10) not null,
wage money not null,
putdate datetime null
)
create table tax
(employeeID int not null,
name varchar(10) not null,
tax money not null,
paydate datetime null
)
go--如果不交税也要写入记录的话,用这个
create trigger cx on wage
for insert
as
insert into tax(employeeID,name,tax,paydate)
select employeeID,name,case when wage<1000 then 0
when wage between 1000 and 5000 then wage*0.01
when wage>5000 then wage*0.03 end,
putdate from inserted
go--如果不交税则不写入交税记录的话,用这个
create trigger cx on wage
for insert
as
insert into tax(employeeID,name,tax,paydate)
select employeeID,name,case when wage between 1000 and 5000 then wage*0.01
when wage>5000 then wage*0.03 end,
putdate from inserted
where wage>=1000
go--不过我想交税工资应该不等于应发工资,所以我想就应该是:
--例如:如果工资在1000~5000 之间,交税工资为工资大于1000的部分,如果是这样则用这个:create trigger cx on wage
for insert
as
insert into tax(employeeID,name,tax,paydate)
select employeeID,name,case when wage between 1000 and 5000 then (wage-1000)*0.01
when wage>5000 then (wage-1000)*0.03 end,
putdate from inserted
where wage>=1000
go
for insert
as
if (select wage from inserted )<1000
insert tax
select employeeid,name,wage*0,putdate from inserted
else if (select wage from inserted )>= 1000 and (select wage from inserted )<=5000
insert tax
select employeeid,name,wage*0.01,putdate from inserted
else if (select wage from inserted )> 5000
insert tax
select employeeid,name,wage*0.03,putdate from inserted