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)
