Javatpoint Logo
Javatpoint Logo

MySQL BEFORE UPDATE Trigger

BEFORE UPDATE Trigger in MySQL is invoked automatically whenever an update operation is fired on the table associated with the trigger. In this article, we are going to learn how to create a before update trigger with its syntax and example.

Syntax

The following is the syntax to create a BEFORE UPDATE trigger in MySQL:

The BEFORE UPDATE trigger syntax parameter are explained as below:

  • First, we will specify the trigger name that we want to create. It should be unique within the schema.
  • Second, we will specify the trigger action time, which should be BEFORE UPDATE. This trigger will be invoked before each row of alterations occurs on the table.
  • Third, we will specify the name of a table to which the trigger is associated. It must be written after the ON keyword. If we did not specify the table name, a trigger would not exist.
  • Finally, we will specify the trigger body that contains a statement for execution when the trigger is activated.

If we want to execute multiple statements, we will use the BEGIN END block that contains a set of queries to define the logic for the trigger. See the below syntax:

Restrictions

  • We cannot update the OLD values in a BEFORE UPDATE trigger.
  • We can change the NEW values.
  • We cannot create a BEFORE UPDATE trigger on a VIEW.

BEFORE UPDATE Trigger Example

Let us understand how to create a BEFORE UPDATE trigger using the CREATE TRIGGER statement in MySQL with an example.

Suppose we have created a table named sales_info as follows:

Next, we will insert some records into the sales_info table as follows:

Then, execute the SELECT statement to see the table data as follows:

MySQL BEFORE UPDATE Trigger

Next, we will use a CREATE TRIGGER statement to create a BEFORE UPDATE trigger. This trigger is invoked automatically before an update event occurs in the table.

If the trigger is created successfully, we will get the output as follows:

MySQL BEFORE UPDATE Trigger

The trigger produces an error message and stops the updation if we update the value in the quantity column to a new value two times greater than the current value.

Let us understand the created trigger in details:

First, we have specified the trigger name as befor_update_salesInfo in the CREATE TRIGGER clause. Second, specify the triggering event and then the table name on which the trigger is associated. Third, we have declared a variable and set its value. Finally, we have specified the trigger body that checks if the new value is two times greater than the old value and then raises an error.

How to call the BEFORE UPDATE trigger?

First, we can use the following statements that update the quantity of the row whose id = 2:

This statement works well because it does not violate the rule. Next, we will execute the below statements that update the quantity of the row as 600 whose id = 2

It will give the error as follows because it violates the rule. See the below output.

MySQL BEFORE UPDATE Trigger

How to create BEFORE UPDATE Trigger in MySQL workbench?

To create a BEFORE UPDATE trigger using MySQL workbench, we first need to launch it and then log in using the username and password we created earlier. We will get the screen as follows:

MySQL BEFORE UPDATE Trigger

Now do the following steps for creating BEFORE UPDATE trigger:

1. Go to the Navigation tab and click on the Schema menu that contains all the databases available in the MySQL server.

2. Select the database (for example, employeedb), double click on it, and display the sub-menu containing Tables, Views, Functions, and Stored Procedures. See the below screen.

MySQL BEFORE UPDATE Trigger

3. Expand the Tables sub-menu and select the table on which you want to create a trigger. After selecting a table, right-click on the selected table (for example, sales_info), and then click on the Alter Table option. See the below image:

MySQL BEFORE UPDATE Trigger

4. Clicking on the Alter Table option gives the screen as below:

MySQL BEFORE UPDATE Trigger

5. Now, click on the Trigger tab shown in the previous section's red rectangular box, then select the Timing/Event BEFORE UPDATE. We will notice that there is a (+) icon button to add a trigger. Clicking on that button, we will get a default code on trigger based on choosing Timing/Event:

MySQL BEFORE UPDATE Trigger

6. Now, complete the trigger code, review them once again, and if no error is found, click on the Apply button.

MySQL BEFORE UPDATE Trigger

7. After clicking on the Apply button, click on the Finish button for completion.

MySQL BEFORE UPDATE Trigger

8. If we take at the schema menu, we will see the trigger sales_info_before_update under the sales_info table as follows:

MySQL BEFORE UPDATE Trigger




Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA