[MDEV-24429] update trigger don't fire on "replace" Created: 2020-12-17  Updated: 2020-12-20  Resolved: 2020-12-20

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - RocksDB
Affects Version/s: 10.5.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Philip orleans Assignee: Alice Sherepa
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Linux


 Description   

This maybe is by design, but it is conceptually a bug.
A "replace" can be an insert or an update, and all update trigger must fire, but they are ignored. This problem makes it more cumbersome to write logic that catches what to do when existing data is there.
Please consider extending update triggers to the statement "replace" when the primary key is there already.

 Comments   
Comment by Alice Sherepa [ 2020-12-17 ]

According to KB here :
The REPLACE statement is executed with the following workflow:

BEFORE INSERT;
BEFORE DELETE (only if a row is being deleted);
AFTER DELETE (only if a row is being deleted);
AFTER INSERT.

So UPDATE trigger will not be fired, but DELETE+INSERT.

Comment by Sergei Golubchik [ 2020-12-20 ]

This is by design.

REPLACE is defined as DELETE+INSERT. It can be internally optimized to be one row operation (UPDATE) instead of two (DELETE+INSERT) but internal optimizations should never have user visible effects. If you have triggers, it will always invoke DELETE and INSERT triggers, because this is how REPLACE is defined.

Generated at Thu Feb 08 09:29:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.