There are some very good reasons why data validation is best performed at the database level rather than at the application level. For instance, the same data source may be accessed by multiple applications. Therefore, you can rely on the data being consistent and valid without having to depend on validation logic being applied on the application side, which might not be consistent across different implementations. Moreover, triggers are ideal for validation because they can be executed before data is inserted or updated. Triggers can also can also prevent a database transaction from being applied while providing an error message.
In today's blog, we'll write a trigger in Navicat Premium that will validate insert operations on a MySQL database table.
Designing the Trigger
We'll be working with the Sakila sample database. It contains a number of related tables themed around a fictional video rental store. Here they are in the Navicat Premium navigation pane:
We'll be adding our trigger to the film table. If you open it in the Designer, you'll see that there are several tabs there:
Clicking the Triggers tab reveals that there are already a few triggers defined for that table. For instance, the ins_film trigger copies film information to the film_text table on data inserts. This is a common task allocated to triggers.
Now we'll add a trigger that will make sure that foreign films are inserted with the original_language_id.
A film's language is actually stored in the language lookup table:
| language_id | name | last_update | ||
| 1 | English | 2006-02-15 05:02:19 | ||
| 2 | Italian | 2006-02-15 05:02:19 | ||
| 3 | Japanese | 2006-02-15 05:02:19 | ||
| 4 | Mandarin | 2006-02-15 05:02:19 | ||
| 5 | French | 2006-02-15 05:02:19 | ||
| 6 | German | 2006-02-15 05:02:19 |
Any language_id other than 1 should have an original_language_id as well. Our trigger will check for a value in the original_language_id column.
- In the Design View of the film table, select the Triggers tab and click on the Add Trigger button.
That will add a new row in the triggers table. - Assign a name of "ins_validate_language", select BEFORE from the Fires drop-down, and click on the Insert checkbox.
- Here's the trigger Body:
BEGIN
IF NEW.language_id != 1 AND NEW.original_language_id IS NULL
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Original language id is required for Foreign language films.';
END IF;
END
Here is our Trigger with all of the fields filled in:
- Click the Save button to create the trigger.
Testing the Trigger
Now it's time to verify that our trigger works as expected. To test it, let's add a new row to the film table with a foreign language_id.
- Open the film table in the Editor.
- Navigate to the last row.
- Select the Form View and click on the Plus (+) button to add a new row:
- In the form, you only need to enter a title and language_id; all other fields have default values or are not required.
- When you click the Add (checkmark) button, you should see our error message:
Conclusion
Triggers are ideal for validation because they can be executed before data is inserted or updated. We saw how a trigger can be employed for validation purposes by writing a trigger in Navicat Premium.
