Triggers should not update table referenced by statement invoking the trigger
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
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
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.