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 AS BasicWage+Bonus-(LateMinus+EarlyLeaveMinus+AbsenceMinus+SickMinus+AffairMinus),--员工月工资
TotalWage money,--员工己领工资
)
(
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 AS BasicWage+Bonus-(LateMinus+EarlyLeaveMinus+AbsenceMinus+SickMinus+AffairMinus),--员工月工资
TotalWage money,--员工己领工资
)
这2张表中可以加个列 部门编号
--员工工资表
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,--员工己领工资
)
--工资总表
create table EmpSumWage
(
EmpSumWageID int primary key,
SumWage money,--企业工资总额
MonthOutWage money,--每月发放工资总额
EmpID int references Wage(EmpID)--外键
OutWage money--已发放工资总额
)
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
Months int unique,--月份怎么可能?
EmpID+Months才唯一!两表怎么关联?第二个表是不是只有一条记录?
--员工工资表
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
*/
------------------------------------------------