php - mysql insert trigger 从一张表中取出数据插入到另一张表中

Question

下面是我的用户月薪表

+-------+------------+
|user_id|user_salary |
+-------+------------+
| 6     | 10000      |
+-------+------------+

这是我的每日考勤表

+-------------+---------+------+-------+---------------+-------+
|attendance_id| user_id |name  |present|attendance_date| wages |
+-------------+---------+------+-------+---------------+-------+
| 1   |       | 6       | parth| P     |2016-12-16     |  350  |  
+-------------+---------+------+-------+---------------+-------+

上面是我的工资和出勤表，我想根据月薪插入每天的工资并使用下面的触发器插入到出勤表是我的触发器代码。我想要 per day=monthly_salary/days_in_month 并插入到出勤工资表中。

trigger wages before insert on attendance 
for each row
set NEW.wages=(select user_salary from salary where user_id=NEW.user_id)/(SELECT DAY(LAST_DAY(now()))) 

Answer 1

您在 BEFORE INSERT ON 行中忘记了命令 CREATE。

一个可行的例子是:

DELIMITER $$
CREATE TRIGGER `trigger` BEFORE INSERT ON `attendance`
FOR EACH ROW
BEGIN
 SET NEW.room = (SELECT user_salary from salary where user_id=NEW.user_id)/(SELECT DAY(LAST_DAY(now())));
END $$
DELIMITER ;