MariaDB/MySQL: ON REPLACE triggers

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):

  1. BEFORE INSERT;
  2. BEFORE DELETE (if a row is being replaced);
  3. AFTER DELETE (if a row is being replaced);
  4. 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 a DELETE 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:

  1. The BEFORE INSERT triggers sets some user variables, that will only be useful if a row will be replaced.
  2. The AFTER INSERT trigger resets these variables.

If a row is being replaced, the following workflow will take place:

  1. The BEFORE INSERT trigger will set some variables.
  2. The BEFORE DELETE trigger will reset them and call xmp_on_replace(); both OLD.* and NEW.* values are passed, thanks to the user variables.
  3. 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

Advertisements

Validating data using a TRIGGER

Versione italiana

I wrote a post about how to emulate the CHECK clause in MariaDB and MySQL using a VIEW. Here is a different approach: this time, we will use TRIGGERs to reach the same goal. And then we will discuss which approach is better.

Let’s CREATE what we need

Let’s CREATE a simple table:

-- make sure that `person` can be created
DROP VIEW   IF EXISTS `person`;
DROP TABLE  IF EXISTS `person`;

-- table whose data must be validated
CREATE TABLE `person`
(
	`id`  mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
	`name`  char(50) NOT NULL,
	`email`  char(80) NOT NULL,
	PRIMARY KEY (`id`)
)
	ENGINE = Aria;

`person` contains a Primary Key, a field called `name`, and `email`. We will just care about the `email`. We want to validate it using a LIKE pattern. But how?

Let’s CREATE the TRIGGER

TRIGGERs can be set on the base DML operations (INSERT, UPDATE, DELETE). If we want to be sure that data are valid, we should use both INSERT and UPDATE TRIGGERs, but for brevity here I will just show the INSERT TRIGGER. TRIGGERs can be executed BEFORE or AFTER the operation they are associated to; of course, if we want to prevent invalid data to be written, we need the BEFORE option.

-- validate email
CREATE TRIGGER `person_validate_insert`
	BEFORE INSERT
	ON `person`
	FOR EACH ROW
BEGIN
	IF NEW.`email` NOT LIKE '%_@%_.__%' THEN
		SIGNAL SQLSTATE VALUE '45000'
			SET MESSAGE_TEXT = '[table:person] - `email` column is not valid';
	END IF;
END;

TRIGGERs can’t have a comment, so I always try to use descriptive names. The first word is person, which is the name of the table; then, I explain what the TRIGGER does: ‘validate insert’.

The TRIGGER checks if the new `email` is valid. If it is, execution continues and the INSERT can be performed. But if it is not, an error is thrown. This stops the execution, and the INSERT will not be performed.

Just a note about the error. The SQLSTATE is ‘45000’, as suggested by MySQL’s documentation – user-defined errors should set SQLSTATE to ‘45000’. The message starts with an info wrapped between the [square brackets]. When I throw an error from a Stored Routine, I write the name of the routine; when I throw it from a TRIGGER, I write the table name. Then, there is a descriptive error message. “Descriptive” means that the user is told what he was trying to do (INSERT) and why the operation aborted (`email` column is not valid).

Let’s test!

I assume that everything I don’t test (if it can be tested), simply does not work. So, let’s INSERT a valid row:

MariaDB [test]> INSERT INTO `person`
    ->          (`name`, `email`)
    ->  VALUES
    ->          ('Emiliano Zapata', 'zapata@revolucion.mx');
Query OK, 1 row affected (0.59 sec)

Great! Now, let’s see what happens when we try to INSERT invalid data:

MariaDB [test]> INSERT INTO `person`
    ->          (`name`, `email`)
    ->  VALUES
    ->          ('John Doe', 'misterdoe@nowhere');
ERROR 1644 (45000): [table:person] - `email` column is not valid

Great! That’s exactly the error we expected to see.

What is the best technique?

Now that I discussed two different techniques (read how to use a VIEW), I am supposed to explain which one is better. But I’ll just write some notes, that should clarify the pro’s and the con’s of each method:

* If you are using MariaDB 5.3 or older, or MySQL 5.4 or older, the SIGNAL statement is not avaible. You can use a SELECT on a table which does not exists, which is a bit less clean (you know why you see that error, but it can be confusing for other developers).
* Hosting companies often do not allow you to use TRIGGERs – in that case, you can only use the VIEW technique.
* The INSERT/UPDATE‘s performance should not be notably affected by this choice. DELETE and SELECT performance may be a little bit slower for the VIEW method.
* The TRIGGER method needs 2 different triggers (on insert, on update; maybe on delete, if you want to check data on other tables to ensure database’s consinstency). The VIEW method needs a view and renamed table. The second option is probably more convenient… but this is TRUE for me, and may be FALSE for you. (a boolean way to say that it’s subjective)
* This also means that the TRIGGER method requires duplicated code (same statements in INSERT and in UPDATE TRIGGERs). You can avoid this issue by using a Stored Routine, but it is slower.

Since there is not an important difference in performances or ease of mantainence of these methods, the choice is up to you.

Enjoy!