Shulou(Shulou.com)06/01 Report--

Navicat Premium is a set of database management tools that allow you to connect to MySQL, MariaDB, SQL Server, SQLite, Oracle and PostgreSQL databases simultaneously using a single program. It is compatible with cloud databases such as Amazon RDS, Amazon Aurora, Amazon Redshift, Microsoft Azure, Oracle Cloud, MongoDB Atlas, Ali Cloud, Tencent Cloud and Huawei Cloud.

There are some very good reasons why data validation is best performed at the database level rather than at the application level. For example, multiple applications can access the same data source. Therefore, you can rely on the consistency and validity of the data, rather than on the validation logic applied on the application side, which may be inconsistent in different implementations. In addition, triggers are ideal for validation because they can be executed before data is inserted or updated. Triggers also prevent database transactions from being applied when error messages are provided.

In this article, we will write a trigger in Navicat Premium to validate inserts on MySQL database tables.

Design trigger

We will use the Sakila sample database. It contains some related tables with the theme of fictional video rental stores. Here, they are in the Navicat Premium navigation pane:

We will add triggers to the movie list. If you open it in Designer, you will see several tabs:

Click the triggers tab to show that some triggers have been defined for the table. For example, an ins_film trigger copies film information into a film_ text table that is inserted into the data. This is a common task assigned to triggers.

Now we will add a trigger to make sure to insert a foreign movie using original_language_id.

The language of the movie is actually stored in the language lookup table:

LANGUAGE_ID name Last Update 1 English 2006-02-15 05LANGUAGE_ID 192 Italy 2006-02-1505VR 02 193 Japan 2006-02-1505V 02 194 Putonghua 2006-02-1505Fran 2006-02-1505Fran 2006-02-1505Swiss German 2006-02-15 05:02:19

Any language_id except 1 should also have original_language_id. Our trigger will check the value in the original_language_id column.

1. In Design View of the movie table, select the trigger tab, and then click the add trigger button.

This adds a new row to the trigger table.

2. Assign the name "ins_validate_language", select BEFORE from the Fires drop-down menu, and then click the Insert check box.

3. This is 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

This is our trigger, filling in all the fields:

4. Click the Save button to create the trigger.

Test trigger

It is time to verify that our triggers are working as expected. To test it, let's add a new row to the movie table using a foreign language language_id.

1. Open the movie table in the editor.

2. Navigate to the last line.

3. Select the form view, and then click the plus (+) button to add a new line:

4. In the form, you only need to enter the title and all other language_id; fields have default values or are not required.

5. When you click the add (check mark) 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 learned how to use triggers for verification purposes by writing triggers in Navicat Premium.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.