There are several reasons why, generally, in MySQL/MariaDB one should not use REPLACE
as a shortcut for SELECT
+ (UPDATE
or INSERT
). One of these reasons is the way REPLACE
fires triggers. Of course it does not fire UPDATE
triggers, and there is no such thing as a REPLACE
trigger; DELETE
and INSERT
triggers are fired instead. What is not obvious is the order in which REPLACE
activates triggers (UPDATE: this beavior was undocumented; I documented it here):
BEFORE INSERT
;BEFORE DELETE
(if a row is being replaced);AFTER DELETE
(if a row is being replaced);AFTER INSERT
.
Knowing this, we can create REPLACE
triggers. Or, better said, we can create a Stored Procedure that is invoked before or after each row’s replacement. It is not exactly easy, but it can be done. Some user variables will help us in two ways:
- They will tell each trigger which triggers have been fired before;
- they will be used to acces
NEW
values in aDELETE
trigger.
Now, take a look at the following example. The notes below will make some points clearer.
CREATE OR REPLACE TABLE xmp ( a INT NOT NULL PRIMARY KEY, b INT NOT NULL ) ENGINE = InnoDB ; DELIMITER || CREATE PROCEDURE xmp_on_replace(IN old_a INT, IN new_a INT, IN old_b INT, IN new_b INT) CONTAINS SQL BEGIN SET @out := CONCAT( 'In column b, we are replacing ', old_b, ' with ', new_b, '. Cool!' ); END || CREATE TRIGGER xmp_bi BEFORE INSERT ON xmp FOR EACH ROW BEGIN SET @maybe_replacing := TRUE; SET @new_a := NEW.a; SET @new_b := NEW.b; END || CREATE TRIGGER xmp_ai AFTER INSERT ON xmp FOR EACH ROW BEGIN SET @maybe_replacing := FALSE; END || CREATE TRIGGER xmp_bd BEFORE DELETE ON xmp FOR EACH ROW BEGIN IF @maybe_replacing THEN SET @maybe_replacing := FALSE; CALL xmp_on_replace(OLD.a, @new_a, OLD.b, @new_b); SET @new_a := NULL; SET @new_b := NULL; END IF; END || DELIMITER ;
When no row is being replaced, the workflow will be the following:
- The
BEFORE INSERT
triggers sets some user variables, that will only be useful if a row will be replaced. - The
AFTER INSERT
trigger resets these variables.
If a row is being replaced, the following workflow will take place:
- The
BEFORE INSERT
trigger will set some variables. - The
BEFORE DELETE
trigger will reset them and call xmp_on_replace(); bothOLD.*
andNEW.*
values are passed, thanks to the user variables. xmp_on_replace()
gives us some information about the row being replaced.
Now, let’s test this example:
MariaDB [test]> INSERT INTO xmp VALUES (2, 0); Query OK, 1 row affected (0.01 sec) MariaDB [test]> REPLACE xmp VALUES (1, 100), (2, 200), (3, 300); Query OK, 4 rows affected (0.01 sec) Records: 3 Duplicates: 1 Warnings: 0 MariaDB [test]> SELECT @out; +-------------------------------------------------+ | @out | +-------------------------------------------------+ | In column b, we are replacing 0 with 200. Cool! | +-------------------------------------------------+ 1 row in set (0.00 sec)
Hey, it works as expected!
Enjoy!
Federico