FAULTNULLlate_timesINT2NULLDEFAULTNULLleave_timesINT2NULLDEFAULTNULLchecki
g_dateDATENULLDEFAULTNULLPRIMARYKEYchecki
g_idINDEXFK_checki
g_employeesemployee_idCONSTRAINTFK_checki
g_employeesFOREIGNKEYemployee_idREFERENCESemployeesemployee_idCOMMENT考勤COLLATEutf8_ge
eral_ciENGINEI
oDB
Daily_satiatio
表：
CREATETABLEdaily_situatio
daily_idVARCHAR10NOTNULLCOMMENTprimarykeychecki
g_idVARCHAR10NULLDEFAULTNULLCOMMENTforeig
keydaily_dateDATENULLDEFAULTNULLshould_timeDATETIMENULLDEFAULTNULLreally_timeDATETIMENULLDEFAULTNULLPRIMARYKEYdaily_idINDEXFK_daily_situatio
_checki
gchecki
g_idCONSTRAINTFK_daily_situatio
_checki
gFOREIGNKEYchecki
g_idREFERENCESchecki
gchecki
g_idCOMMENT每日情况COLLATEutf8_ge
eral_ci
fENGINEI
oDB
Salary表：
CREATETABLEsalarysalary_idVARCHAR10NOTNULLCOMMENTprimarykeyemployee_idVARCHAR10NOTNULLCOMMENTforeig
keybo
us_pctDOUBLENOTNULLDEFAULT01COMMENT奖金率give_dateDATENULLDEFAULTNULLshould_salaryINT11NULLDEFAULTNULLCOMMENT大于0really_salaryINT11NULLDEFAULTNULLCOMMENT大于0PRIMARYKEYsalary_idINDEXFK_salary_employeesemployee_idCONSTRAINTFK_salary_employeesFOREIGNKEYemployee_idREFERENCESemployeesemployee_idCOMMENT工资COLLATEutf8_ge
eral_ciENGINEI
oDB
触发器设计：（1）每日情况触发器：当应到时间实到时间时，迟到次数自动加1
CREATEDEFINERrootlocalhostTRIGGERdaily_situatio
_after_updateAFTERUPDATEONdaily_situatio
FOREACHROWBEGINUPDATEchecki
gcdaily_situatio
dSETclate_timesclate_times1WHEREdshould_timedreally_timea
ddchecki
g_idcchecki
g_ida
ddate_formatdshould_timeYmddate_formatdreally_timeYmdEND
功能演示：第一个人1101的11月份考勤情况中起初没有迟到
ff现把他的第一天情况改成迟到
f触发器响应，自动修改checki
g表，使得第一个人的迟到次数加1
f（2）考勤_工资触发器：实发工资应发工资应发工资奖金率迟到次数50早退次数20请假次数10
CREATEDEFINERrootlocalhostTRIGGERchecki
g_salaryAFTERUPDATEONchecki
gFOREACHROWBEGINupdatesalaryschecki
gcsetreally_salary09should_salaryshould_salarybo
us_pctselectlate_timesfromchecki
gcwheresemployee_idcemployee_id50selectleave_timesfromchecki
gcwheresemployee_idcemployee_id20selectvacate_timesfromchecki
gcwheresemployee_idcemployee_id10wheresgive_datecchecki
g_dateEND
功能演示：第一个人在11月份的考核中，迟到了一次工资09（100001000001150）9945
f现更改20141101李四的考勤表，迟到改为5次，早退改为3次r