Bug #79141 Triggers cannot use NEW.c_name or use OLD.c_name to refer to generated columns
Submitted: 5 Nov 2015 15:59
Reporter: William Chiquito Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7.9 OS:Any
Assigned to:
Tags: generated-columns, new, old, triggers

[5 Nov 2015 15:59] William Chiquito
Description:
The documentation says: "Triggers cannot use NEW.col_name or use OLD.col_name to refer to generated columns.":

* 13.1.14 CREATE TABLE Syntax -> CREATE TABLE and Generated Columns - https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns

* 13.1.16 CREATE TRIGGER Syntax - https://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

However, I have access to the column generated from the trigger.

How to repeat:
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.9     |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE  IF EXISTS  `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t1` (
    ->   `c0` INTEGER UNSIGNED NOT NULL PRIMARY KEY,
    ->   `c1` INTEGER UNSIGNED GENERATED ALWAYS AS (`c0`) VIRTUAL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE  IF EXISTS  `t2`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `t2` (
    ->   `action` VARCHAR(20),
    ->   `c1`     INTEGER UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ||

mysql> DROP TRIGGER IF EXISTS `t1_ins_bef`||
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TRIGGER `t1_ins_bef` BEFORE INSERT ON `t1`
    -> FOR EACH ROW
    -> BEGIN
    ->   SET NEW.`c1` := 10 * 5;
    ->   INSERT INTO `t2` (`action`, `c1`) VALUES ('BEFORE INSERT', NEW.`c1` * 2);
    -> END||
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TRIGGER IF EXISTS `t1_ins_aft`||
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TRIGGER `t1_ins_aft` AFTER INSERT ON `t1`
    -> FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO `t2` (`action`, `c1`) VALUES ('AFTER INSERT', NEW.`c1` * 3);
    -> END||
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TRIGGER IF EXISTS `t1_del_bef`||
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TRIGGER `t1_del_bef` BEFORE DELETE ON `t1`
    -> FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO `t2` (`action`, `c1`) VALUES ('BEFORE DELETE', OLD.`c1` * 4);
    -> END||
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TRIGGER IF EXISTS `t1_del_aft`||
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TRIGGER `t1_del_aft` AFTER DELETE ON `t1`
    -> FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO `t2` (`action`, `c1`) VALUES ('AFTER DELETE', OLD.`c1` * 5);
    -> END||
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> INSERT INTO `t1` (`c0`) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT `c0`, `c1` FROM t1;
+----+------+
| c0 | c1   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

mysql> DELETE FROM t1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT `action`, `c1` FROM t2;
+---------------+-----+
| action        | c1  |
+---------------+-----+
| BEFORE INSERT | 100 |
| AFTER INSERT  |   3 |
| BEFORE DELETE |   4 |
| AFTER DELETE  |   5 |
+---------------+-----+
4 rows in set (0.00 sec)