dolthub / dolthub/dolt

Triggers should not update table referenced by statement invoking the trigger

Open
#9,907 1 comment 0 reactions 1 assignee Claimed by @angelamayxie View on GitHub
analyzer correctness sql
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.