改一下:[code=SQL]
--员工工资表
create table Wage
(
EmpID int primary key,--员工编号
EmpName varchar(20),--员工姓名
Months int unique,--月份
BasicWage money,--基本工资
Bonus money,--奖金
LateMinus money,--迟到罚款
EarlyLeaveMinus money,--早退罚款
AbsenceMinus money,--缺勤罚款
SickMinus money,--病假罚款
AffairMinus money,--事件罚款
MonthTotalWage money,--员工月工资
TotalWage money,--员工己领工资
EmpSumWageID int references EpSumWage(EmpSumWageID)
)
--工资总表
create table EmpSumWage
(
EmpSumWageID int primary key,
SumWage money,--企业工资总额
MonthOutWage money,--每月发放工资总额
OutWage money--已发放工资总额
)------------------------------------------------
/*
实现以下功能:
1.记录员工每月的工资:MonthTotalWage=BasicWage+Bonus-(LateMinus+EarlyLeaveMinus+AbsenceMinus+SickMinus+AffairMinus)
2.记录员工已领的工资,最多为12个月:TotalWage=1月+2月+...+12月
3.记录企业每月发放的工资总额:MonthOutWage=所有员工在当月的MonthTotalWage
4.记录企业已发放的工资总额:OutWage=SumWage-所有员工的TotalWage
5.对企业工资总额进行更新:SumWage=SumWage-OutWage
*/
------------------------------------------------[/code]
--员工工资表
create table Wage
(
EmpID int primary key,--员工编号
EmpName varchar(20),--员工姓名
Months int unique,--月份
BasicWage money,--基本工资
Bonus money,--奖金
LateMinus money,--迟到罚款
EarlyLeaveMinus money,--早退罚款
AbsenceMinus money,--缺勤罚款
SickMinus money,--病假罚款
AffairMinus money,--事件罚款
MonthTotalWage money,--员工月工资
TotalWage money,--员工己领工资
EmpSumWageID int references EpSumWage(EmpSumWageID)
)
--工资总表
create table EmpSumWage
(
EmpSumWageID int primary key,
SumWage money,--企业工资总额
MonthOutWage money,--每月发放工资总额
OutWage money--已发放工资总额
)------------------------------------------------
/*
实现以下功能:
1.记录员工每月的工资:MonthTotalWage=BasicWage+Bonus-(LateMinus+EarlyLeaveMinus+AbsenceMinus+SickMinus+AffairMinus)
2.记录员工已领的工资,最多为12个月:TotalWage=1月+2月+...+12月
3.记录企业每月发放的工资总额:MonthOutWage=所有员工在当月的MonthTotalWage
4.记录企业已发放的工资总额:OutWage=SumWage-所有员工的TotalWage
5.对企业工资总额进行更新:SumWage=SumWage-OutWage
*/
------------------------------------------------[/code]
create trigger up_tb on Wage
for update
as
begin
----1 记录员工每月的工资:MonthTotalWage=BasicWage+Bonus-(LateMinus+EarlyLeaveMinus+AbsenceMinus+SickMinus+AffairMinus)
update a
set a.MonthTotalWage=b.BasicWage+b.Bonus-(b.LateMinus+b.EarlyLeaveMinus+b.AbsenceMinus+b.SickMinus+b.AffairMinus)
from Wage a,inserted b
where a.EmpID=b.EmpID
----2 记录员工已领的工资,最多为12个月:TotalWage=1月+2月+...+12月
update a
set a.TotalWage=(select sum(b.MonthTotalWage) from wage b,inserted c where b.empid=c.empid and b.Months between 1 and c.Months)
from wage a,inserted d
where a.empid=d.empid
-----3记录企业每月发放的工资总额:MonthOutWage=所有员工在当月的MonthTotalWage
update a
set a.MonthOutWage=(select sum(MonthTotalWage) from wage c,inserted d where c.moenth=d.months)
from EmpSumWage a,inserted b
where a.EmpSumWageID=b.EmpSumWageID and a.月份字段=b.months -----感觉 工资总表少个月份字段------4 记录企业已发放的工资总额:OutWage=SumWage-所有员工的TotalWage
update a
set a.OutWage=a.SumWage-(select sum(TotalWage) from wage c,inserted d where c.moenth=d.months)
from EmpSumWage a,inserted b
where a.EmpSumWageID=b.EmpSumWageID and a.月份字段=b.months -----感觉 工资总表少个月份字段
-------5 对企业工资总额进行更新:SumWage=SumWage-OutWage
不知道EmpSumWageID 字段是什么意思? 现在也不确定 3,4 是否正确。
end
1.统计员工过去12个月所发的总工资:你并没有指明年份,如何统计过去12个月?假设某员工某年度的某月未发工资(停薪留职),但如果按你的1月,2月,3月...这样统计,不就要统计到上个年度的这个月上了么(因为你表中没有年份).
2.工资总表中,是一条记录,还是有多条记录?如果只有一条记录,那还要表干嘛?要数据时直接从工资表中统计不就行了,如果是多条记录,那是什么样的记录?每条记录代表什么含义?
3.对企业工资总额进行更新:SumWage=SumWage-OutWage,第一次统计的企业工资总额是多少?或者,什么叫做企业工资总额?弄一堆钱在那儿,发一点,扣一点?似乎不是这么处理的吧,如果说工资总额是从工资表中统计求和而来还能说说,可你这儿做减法又是什么意思呢?
总之,建议楼主把表设计的意思告诉我们,不然这个触发器写出来也不一定符合楼主的要求.
--员工工资表
create table Wage
(
EmpID int primary key,--员工编号
EmpName varchar(20),--员工姓名
Months int unique,--月份
BasicWage money,--基本工资
Bonus money,--奖金
LateMinus money,--迟到罚款
EarlyLeaveMinus money,--早退罚款
AbsenceMinus money,--缺勤罚款
SickMinus money,--病假罚款
AffairMinus money,--事件罚款
MonthTotalWage money,--员工月工资
TotalWage money,--员工己领工资
EmpSumWageID int references EpSumWage(EmpSumWageID)
)
--工资总表
create table EmpSumWage
(
EmpSumWageID int primary key,
Months int unique,
SumWage money,--企业工资总额
MonthOutWage money,--每月发放工资总额
OutWage money--已发放工资总额
)------------------------------------------------
/*
实现以下功能:
1.记录员工每月的工资:MonthTotalWage=BasicWage+Bonus-(LateMinus+EarlyLeaveMinus+AbsenceMinus+SickMinus+AffairMinus)
2.记录员工已领的工资,最多为12个月:TotalWage=1月+2月+...+12月
3.记录企业每月发放的工资总额:MonthOutWage=所有员工在当月的MonthTotalWage
4.记录企业已发放的工资总额:OutWage=SumWage-所有员工的TotalWage
5.对企业工资总额进行更新:SumWage=SumWage-OutWage
*/
------------------------------------------------
EmpID int primary key,--员工编号
Months int unique,--月份
很难理解,不可能EmpID唯一Months唯一,而是两个组合唯一
工资总表如果有月份,或许结构就清楚了
还有Months也应该是不是唯一的,因为不同员工有相同月份.
那根据大哥的意思,应该怎么改下。帮个忙。我是新手,没有办法。
for insert,update,delete
as
begin
----1 记录员工每月的工资:MonthTotalWage=BasicWage+Bonus-(LateMinus+EarlyLeaveMinus+AbsenceMinus+SickMinus+AffairMinus)
update a
set a.MonthTotalWage=a.BasicWage+a.Bonus-(a.LateMinus+a.EarlyLeaveMinus+a.AbsenceMinus+a.SickMinus+a.AffairMinus)
from Wage a,inserted b
where a.EmpID=b.EmpID
and a.Months=b.Months
update a
set a.MonthTotalWage=a.BasicWage+a.Bonus-(a.LateMinus+a.EarlyLeaveMinus+a.AbsenceMinus+a.SickMinus+a.AffairMinus)
from Wage a,deleted b
where a.EmpID=b.EmpID
and a.Months=b.Months
----2 记录员工已领的工资,最多为12个月:TotalWage=1月+2月+...+12月
update a
set a.TotalWage=(select sum(b.MonthTotalWage) from wage b where b.empid=a.empid and b.Months between 1 and a.Months)
from wage a,inserted d
where a.empid=d.empid
and a.Months>=d.Months
update a
set a.TotalWage=(select sum(b.MonthTotalWage) from wage b where b.empid=a.empid and b.Months between 1 and a.Months)
from wage a,deleted d
where a.empid=d.empid
and a.Months>=d.Months
-----3记录企业每月发放的工资总额:MonthOutWage=所有员工在当月的MonthTotalWage
------4 记录企业已发放的工资总额:OutWage=SumWage-所有员工的TotalWage
-------5 对企业工资总额进行更新:SumWage=SumWage-OutWage
update a set
a.MonthOutWage=a.MonthOutWage + t.MonthOutWage
,a.OutWage=a.OutWage + t.OutWage
,SumWage=a.SumWage - t.OutWage
from EmpSumWage a,(
select Months
,MonthOutWage = sum(BasicWage)+sum(Bonus)-(sum(LateMinus)+sum(EarlyLeaveMinus)+sum(AbsenceMinus)+sum(SickMinus)+sum(AffairMinus))
,OutWage = -sum(TotalWage)
from (
select Months,BasicWage,Bonus,LateMinus,EarlyLeaveMinus,AbsenceMinus,SickMinus,AffairMinus,TotalWage
from inserted
union all
select Months,-BasicWage,-Bonus,-LateMinus,-EarlyLeaveMinus,-AbsenceMinus,-SickMinus,-AffairMinus,-TotalWage
from deleted
) as t1
group by Months
) as t
where a.月份字段=t.months -----感觉 工资总表少个月份字段
end
create table Wage
(
EmpID int primary key,--员工编号
EmpName varchar(20),--员工姓名
Months int unique,--月份
BasicWage money,--基本工资
Bonus money,--奖金
LateMinus money,--迟到罚款
EarlyLeaveMinus money,--早退罚款
AbsenceMinus money,--缺勤罚款
SickMinus money,--病假罚款
AffairMinus money,--事件罚款
MonthTotalWage money,--员工月工资
TotalWage money,--员工己领工资
EmpSumWageID int references EpSumWage(EmpSumWageID)
)
--工资总表
create table EmpSumWage
(
EmpSumWageID int primary key,
Months int unique,--月份
SumWage money,--企业工资总额
MonthOutWage money,--每月发放工资总额
OutWage money--已发放工资总额
)