| Bug #91687 | unknown column error | ||
|---|---|---|---|
| Submitted: | 18 Jul 2018 2:08 | Modified: | 18 Jul 2018 8:43 |
| Reporter: | P Satish Patro | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Other (win 10) |
| Assigned to: | CPU Architecture: | Any | |
[18 Jul 2018 8:43]
MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. Please see https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated... mysql> DELIMITER $$ mysql> CREATE TRIGGER before_employee_insert -> BEFORE INSERT ON employee -> FOR EACH ROW -> BEGIN -> IF grade in('M', 'L', 'T') THEN -> SET NEW.grade = 'W'; -> END IF; -> END $$ Query OK, 0 rows affected (0.01 sec) mysql> DELIMITER ; mysql> insert into employee values(111, 'yyz', 20000, 'S')$$ -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$' at line 1 mysql> mysql> insert into employee values(111, 'yyz', 20000, 'S'); ERROR 1054 (42S22): Unknown column 'grade' in 'field list' mysql> mysql> drop trigger before_employee_insert; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER $$ mysql> CREATE TRIGGER before_employee_insert -> BEFORE INSERT ON employee -> FOR EACH ROW -> BEGIN -> IF NEW.grade in('M', 'L', 'T') THEN -> SET NEW.grade = 'W'; -> END IF; -> END $$ Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> mysql> insert into employee values(111, 'yyz', 20000, 'S'); Query OK, 1 row affected (0.00 sec) mysql> select * from employee; +-------+---------+-----------+-------+ | empId | empName | empSalary | grade | +-------+---------+-----------+-------+ | 111 | yyz | 20000 | S | +-------+---------+-----------+-------+ 1 row in set (0.00 sec) mysql> insert into employee values(111, 'yyz', 20000, 'M'); Query OK, 1 row affected (0.00 sec) mysql> select * from employee; +-------+---------+-----------+-------+ | empId | empName | empSalary | grade | +-------+---------+-----------+-------+ | 111 | yyz | 20000 | S | | 111 | yyz | 20000 | W | +-------+---------+-----------+-------+ 2 rows in set (0.00 sec) Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.
Description: I created a table employee DELIMITER is $$ mysql> select * from employee$$ +-------+---------+-----------+-------+ | empId | empName | empSalary | grade | +-------+---------+-----------+-------+ | 1 | abc | 11500 | M | | 2 | def | 23000 | L | | 3 | ghi | 34500 | T | | 4 | jkl | 46000 | X | | 5 | mno | 57500 | M | | 6 | pqr | 69000 | L | | 7 | stu | 80500 | X | | 8 | tuv | 86250 | T | | 9 | uvw | 92000 | X | | 10 | wxy | 97750 | M | | 11 | xyy | 103500 | T | | 12 | xyz | 109250 | NULL | +-------+---------+-----------+-------+ and one trigger mysql> CREATE TRIGGER before_employee_insert -> BEFORE INSERT ON employee -> FOR EACH ROW -> BEGIN -> IF grade in('M', 'L', 'T') THEN -> SET NEW.grade = 'W'; -> END IF; -> END $$ And when I am inserting any values to it like mysql> insert into employee values(111, 'yyz', 20000, 'S')$$ ERROR 1054 (42S22): Unknown column 'grade' in 'field list' It's not allowing to store any value. It was allowing before creating trigger How to repeat: I created a table employee DELIMITER is $$ mysql> select * from employee$$ +-------+---------+-----------+-------+ | empId | empName | empSalary | grade | +-------+---------+-----------+-------+ | 1 | abc | 11500 | M | | 2 | def | 23000 | L | | 3 | ghi | 34500 | T | | 4 | jkl | 46000 | X | | 5 | mno | 57500 | M | | 6 | pqr | 69000 | L | | 7 | stu | 80500 | X | | 8 | tuv | 86250 | T | | 9 | uvw | 92000 | X | | 10 | wxy | 97750 | M | | 11 | xyy | 103500 | T | | 12 | xyz | 109250 | NULL | +-------+---------+-----------+-------+ and one trigger mysql> CREATE TRIGGER before_employee_insert -> BEFORE INSERT ON employee -> FOR EACH ROW -> BEGIN -> IF grade in('M', 'L', 'T') THEN -> SET NEW.grade = 'W'; -> END IF; -> END $$ And when I am inserting any values to it like mysql> insert into employee values(111, 'yyz', 20000, 'S')$$ ERROR 1054 (42S22): Unknown column 'grade' in 'field list' It's not allowing to store any value. It was allowing before creating trigger