Triggers should not update table referenced by statement invoking the trigger #9907
Description
Dolt currently doesn't produce an error when a trigger references a table referenced by statement invoking the trigger, whereas MySQL and MariaDB do.
MySQL
mysql> create table parent(id char(36) not null default (uuid()), primary key (id));
Query OK, 0 rows affected (0.00 sec)
mysql> create table child (parent_id char(36) not null, version_id char(36) not null default (uuid()), primary key (parent_id, version_id), constraint fk_child_parent foreign key (parent_id) references parent(id));
Query OK, 0 rows affected (0.02 sec)
mysql> create trigger trg_child_after_insert after insert on child for each row update parent set id=id where id=new.parent_id;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into parent () values ();
Query OK, 1 row affected (0.00 sec)
mysql> insert into child (parent_id) values ((select id from parent limit 1));
ERROR 1442 (HY000): Can't update table 'parent' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
MariaDB
mysql> create table parent (id int not null auto_increment, primary key (id));
Query OK, 0 rows affected (0.01 sec)
mysql> create table child (parent_id int not null, version_id int not null auto_increment, primary key(version_id));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into parent () values ();
Query OK, 1 row affected (0.00 sec)
mysql> create trigger trg_child_after_insert after insert on child for each row update parent set id = 7 where id = NEW.parent_id;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into child (parent_id) values ((select id from parent limit 1)) returning parent_id, version_id;
ERROR 1442 (HY000): Can't update table 'parent' in stored function/trigger because it is already used by statement which invoked this stored function/trigger