我有两个表,Employee和Department
员工表有以下列
Name
SSN
Salary
Dept_No
Supervisor_SSN
部门表有以下列
Dept_Name
Dept_No
Total_Salary
Manager_SSN
我需要编写一个触发器,以便在插入新员工时,我们更新部门总工资以包括新员工的工资。这就是我想到的,但显然触发器中不允许使用"WHERE"。感谢您的帮助。
USE `mydb`;
DELIMITER $$
CREATE TRIGGER `Employee_BINS` BEFORE INSERT ON `Employee` FOR EACH ROW
WHERE NEW.dno = department.Dno
SET department.total_sal = department.total_sal + NEW.salary;
我得到的错误是
syntax error, unexpected WHERE
FOR EACH ROW
之后,您应该像在存储过程中一样编写普通的sql语句。
有关语法的更多信息,请访问:https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html
您应该像sample format
:一样提到的创建普通sql查询
你应该提到正确的格式delimiter
和for each row
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
-> FOR EACH ROW
-> BEGIN
-> IF NEW.amount < 0 THEN
-> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN
-> SET NEW.amount = 100;
-> END IF;
-> END;//
mysql> delimiter ;
https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html
https://dev.mysql.com/doc/refman/5.0/en/create-trigger.html