- 触发器示例1:当
Employees
表中插入新员工时,自动创建一个薪资支付记录(即使金额为0),用于记录员工开始有薪资记录的时间点。DELIMITER // CREATE TRIGGER after_employee_insert AFTER INSERT ON Employees FOR EACH ROW BEGIN INSERT INTO SalaryPayments (payment_date, total_amount) VALUES (NEW.hire_date, 0); -- 注意:此处假设SalaryPayments表中的payment_date字段可以存储0金额的记录 -- 在实际应用中,可能需要更复杂的逻辑来处理这种情况 END// DELIMITER ;
触发器示例3:当
Employees
表中删除员工时,删除该员工在SalaryDetails
、SalaryPaymentDetails
和(可选的)SalaryPayments
中的相关记录DELIMITER // CREATE TRIGGER after_employee_delete AFTER DELETE ON Employees FOR EACH ROW BEGIN DELETE FROM SalaryDetails WHERE employee_id = OLD.employee_id; DELETE FROM SalaryPaymentDetails WHERE employee_id = OLD.employee_id; -- 可选:如果确定要删除员工的所有薪资支付记录(包括其他员工的记录),则取消注释以下行 -- DELETE FROM SalaryPayments WHERE EXISTS ( -- SELECT 1 FROM SalaryPaymentDetails WHERE SalaryPayments.payment_id = SalaryPaymentDetails.payment_id AND employee_id = OLD.employee_id -- ); END// DELIMITER ;
- 触发器示例6:当
SalaryPayments
表中插入新的薪资支付记录时,自动在SalaryPaymentDetails
表中为每位员工创建支付详情记录(假设每位员工在支付日期都会收到薪资)。DELIMITER // CREATE TRIGGER after_salary_payment_insert AFTER INSERT ON SalaryPayments FOR EACH ROW BEGIN INSERT INTO SalaryPaymentDetails (payment_id, employee_id, amount) SELECT NEW.payment_id, e.employee_id, 0 -- 初始假设金额为0,实际业务中可能需要根据其他表的数据来设置金额 FROM Employees e WHERE NOT EXISTS ( SELECT 1 FROM SalaryPaymentDetails WHERE payment_id = NEW.payment_id AND employee_id = e.employee_id ); -- 注意:这里假设在插入薪资支付记录时,需要为所有员工创建支付详情记录,并且假设初始金额为0 -- 实际业务中可能需要根据SalaryDetails或其他表的数据来设置每位员工的支付金额 END// DELIMITER ;
【无标题】
最新推荐文章于 2024-08-09 21:25:57 发布