mysql - 我在使用这个 sql 触发器时遇到问题

Question

我正在编写一个触发器，当新员工被雇用时，它会在工资表中将其工资设置为 50000。到目前为止，我启动了触发器，但不知道如何制作 IF 语句。一些帮助将不胜感激。

EMPLOYEE:
    +-----------+----------+------+-----+---------+-------+
    | Field     | Type     | Null | Key | Default | Extra |
    +-----------+----------+------+-----+---------+-------+
    | staff_id  | int(11)  | NO   |PRI  | NULL    |       | 
    | seniority | int(11)  | YES  |     | NULL    |       | 
    | lastname  | char(30) | YES  |     | NULL    |       | 
    | firstname | char(30) | YES  |     | NULL    |       |
    +-----------+----------+------+-----+---------+-------+

SALARY:
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| staff_id  | int(11)  | NO   |PRI  | NULL    |       | 
| salary    | int(16)  | YES  |     | NULL    |       | 
+-----------+----------+------+-----+---------+-------+
delimiter $$

Create Trigger Salary_add
   BEFORE INSERT ON employee
   FOR EACH ROW
BEGIN
   DECLARE num_row INTEGER;
   DECLARE tot_rows INTEGER;

SELECT COUNT(*)
   INTO tot_rows
   FROM employee
   WHERE staff_id=NEW.staff_id;

SELECT COUNT(*)
   INTO num_row
   FROM salary
   WHERE staff_id=NEW.staff_id;

IF num_row > 0 THEN
    UPDATE employee

    WHERE staff_id = NEW.staff_id;

ELSE
INSERT INTO employee
    (staff_id, seniority, lastname, firstname)
    VALUES(NEW.staff_id, NEW.seniority, NEW.lastname, NEW.firstname);
END IF


end$$

Answer 1

你在找这个吗？

CREATE TRIGGER salary_add 
AFTER INSERT ON employee
FOR EACH ROW 
INSERT INTO salary VALUES (NEW.staff_id, 50000);

这是一个 SQLFiddle 演示