|
|
-- 文件名：triggers.sql
|
|
|
-- 这是为员工考勤管理系统编写的触发器创建脚本
|
|
|
|
|
|
DELIMITER //
|
|
|
|
|
|
-- 1. 触发器：trg_before_insert_attendance
|
|
|
-- 需求：在插入考勤记录前，自动计算工作时长和加班时长
|
|
|
CREATE TRIGGER trg_before_insert_attendance
|
|
|
BEFORE INSERT ON Attendance
|
|
|
FOR EACH ROW
|
|
|
BEGIN
|
|
|
DECLARE work_duration INT;
|
|
|
DECLARE overtime_duration INT;
|
|
|
|
|
|
-- NULL 检查
|
|
|
IF NEW.CheckInTime IS NULL OR NEW.CheckOutTime IS NULL THEN
|
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '上班时间或下班时间不能为空';
|
|
|
END IF;
|
|
|
|
|
|
-- 计算工作时长 (下班时间 - 上班时间)，单位为小时
|
|
|
SET work_duration = TIMESTAMPDIFF(MINUTE, NEW.CheckInTime, NEW.CheckOutTime) / 60;
|
|
|
|
|
|
-- 如果工作时长大于8小时，计算加班时长
|
|
|
IF work_duration > 8 THEN
|
|
|
SET overtime_duration = work_duration - 8;
|
|
|
SET NEW.OvertimeHours = overtime_duration;
|
|
|
ELSE
|
|
|
SET NEW.OvertimeHours = 0;
|
|
|
END IF;
|
|
|
|
|
|
SET NEW.WorkingHours = work_duration;
|
|
|
END //
|
|
|
|
|
|
-- 2. 触发器：trg_after_update_leave_status
|
|
|
-- 需求：当假期申请的状态被更新为“已批准”时，自动更新员工的状态为“休假”
|
|
|
CREATE TRIGGER trg_after_update_leave_status
|
|
|
AFTER UPDATE ON LeaveRequest
|
|
|
FOR EACH ROW
|
|
|
BEGIN
|
|
|
-- 如果请假申请已批准，且员工当前状态不是“休假”
|
|
|
IF NEW.RequestStatus = '已批准' AND (SELECT EmploymentStatus FROM Employee WHERE EmployeeID = NEW.EmployeeID) != '休假' THEN
|
|
|
UPDATE Employee
|
|
|
SET EmploymentStatus = '休假'
|
|
|
WHERE EmployeeID = NEW.EmployeeID;
|
|
|
END IF;
|
|
|
END //
|
|
|
|
|
|
-- 3. 触发器：trg_before_insert_performance_appraisal
|
|
|
-- 需求：在插入绩效考核记录前，确保绩效评分为合法值（例如：0到100之间）
|
|
|
CREATE TRIGGER trg_before_insert_performance_appraisal
|
|
|
BEFORE INSERT ON PerformanceAppraisal
|
|
|
FOR EACH ROW
|
|
|
BEGIN
|
|
|
-- 如果绩效评分不在0到100之间，抛出错误
|
|
|
IF NEW.ReviewScore < 0 OR NEW.ReviewScore > 100 THEN
|
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '绩效评分必须在0到100之间';
|
|
|
END IF;
|
|
|
END //
|
|
|
|
|
|
-- 4. 触发器：trg_after_insert_employee
|
|
|
-- 需求：当插入新员工记录时，自动创建一个默认的用户登录记录
|
|
|
CREATE TRIGGER trg_after_insert_employee
|
|
|
AFTER INSERT ON Employee
|
|
|
FOR EACH ROW
|
|
|
BEGIN
|
|
|
-- 插入新的用户登录记录
|
|
|
INSERT INTO UserLogin (EmployeeID, Username, Password, AccountStatus)
|
|
|
VALUES (NEW.EmployeeID, CONCAT('user_', NEW.EmployeeID), 'default_password', '有效');
|
|
|
END //
|
|
|
|
|
|
-- 5. 触发器：trg_before_delete_attendance
|
|
|
-- 需求：在删除考勤记录前，检查是否存在未处理的加班记录。如果有，则不允许删除该记录
|
|
|
CREATE TRIGGER trg_before_delete_attendance
|
|
|
BEFORE DELETE ON Attendance
|
|
|
FOR EACH ROW
|
|
|
BEGIN
|
|
|
-- 检查该记录是否存在加班时长
|
|
|
IF OLD.OvertimeHours > 0 THEN
|
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '存在未处理的加班时长，不能删除考勤记录';
|
|
|
END IF;
|
|
|
END //
|
|
|
|
|
|
|
|
|
DELIMITER ;
|
