> ## Documentation Index
> Fetch the complete documentation index at: https://kb.hosting.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Using MySQL triggers

> Learn how to create MySQL triggers with this complete walkthrough including relevant code snippets, detailed instructions, and links to related articles.

This article describes how to create and use MySQL triggers on your hosting.com account. Triggers are predefined rules associated with a table. They can be called ("triggered") before or after a SQL statement inserts, updates, or deletes data from the associated table.

You can use triggers in a wide range of scenarios. For example, you could use a trigger to automatically change certain values when a row is updated. Or you could specify a set of actions to take whenever a row is deleted. The possibilities are almost endless.

## Creating and using triggers

You can create and use triggers on any hosting.com server that uses MySQL.

### Setting up a test database

To demonstrate a basic example of a trigger in action, let's start by creating a database for testing purposes. In the following SQL statement, replace *username* with your account username:

```
CREATE DATABASE username_test;
```

<Note>
  You can run the previous SQL command (and the following SQL commands) from the command line [using the MySQL tool](/docs/connect-to-mysql-from-the-command-line), or in your web browser [using phpMyAdmin](/docs/phpmyadmin-and-phppgadmin).
</Note>

If you are using phpMyAdmin, click the name **username\_test** to select the database. Otherwise, if you are using the *mysql* command-line program, type the following SQL statement:

```
USE username_test;
```

Next, create a table in the **username\_test** database named **products**. To do this, run the following SQL statement:

```
CREATE TABLE products (prod_id INT NOT NULL AUTO_INCREMENT, prod_name VARCHAR(20) NOT NULL, prod_cost FLOAT NOT NULL DEFAULT 0.0, prod_price FLOAT NOT NULL DEFAULT 0.0, PRIMARY KEY(prod_id));
```

The following SQL statement adds some sample data to the **products** table:

```
INSERT INTO products (prod_name, prod_cost, prod_price) VALUES ('Basic Widget',5.95,8.35),('Micro Widget',0.95,1.35),('Mega Widget',99.95,140.00);
```

Now we're ready to create a trigger for our table!

### Creating the trigger

Let's create a trigger named **updateProductPrice**. This particular trigger is activated whenever the **products** table is updated. When this event occurs, the trigger checks each row to see if the product cost (**prod\_cost**) value is being changed. If it is, then the trigger automatically sets the item's new price (**prod\_price**) to 1.40 times the item's new cost (in other words, a 40% markup).

To create this trigger, run the following MySQL statements:

```
DELIMITER $$
CREATE TRIGGER `updateProductPrice`
BEFORE UPDATE ON `products`
FOR EACH ROW
BEGIN
  IF NEW.prod_cost < OLD.prod_cost
    THEN
      SET NEW.prod_price = NEW.prod_cost * 1.40;
  END IF;
END$$
DELIMITER;
```

<Note>
  The **DELIMITER** command at the beginning of these statements prevents MySQL from processing the trigger definition too soon. The **DELIMITER** command at the end of these statements returns processing to normal.
</Note>

### Using the trigger

The **updateProductPrice** trigger is now ready to be invoked automatically whenever a row in the **products** table is updated. For example, run the following SQL statement to change the cost of the Basic Widget:

```
UPDATE products SET prod_cost = 7.00 WHERE prod_id = 1;
```

When you run this SQL statement, the trigger activates as well, and automatically updates the Basic Widget's price in proportion to the new cost. To verify this, you can run the following SQL statement:

```
SELECT * FROM products;
```

This statement returns the following results:

```
+---------+--------------+-----------+------------+
| prod_id | prod_name    | prod_cost | prod_price |
+---------+--------------+-----------+------------+
|       1 | Basic Widget |         7 |        9.8 |
|       2 | Micro Widget |      0.95 |       1.35 |
|       3 | Mega Widget  |     99.95 |        140 |
+---------+--------------+-----------+------------+
3 rows in set (0.00 sec)
```

As you can see, the **updateProductPrice** trigger has automatically updated the Basic Widget's price (9.80) based on the new cost (7.00). Although this is a simple example of what a trigger can do, you can use the same techniques in your own databases -- the possibilities are almost endless.

* For more information about MySQL triggers, please visit [https://dev.mysql.com/doc/refman/8.4/en/triggers.html](https://dev.mysql.com/doc/refman/8.4/en/triggers.html).

* For more information about the **CREATE TRIGGER** statement, please visit [https://dev.mysql.com/doc/refman/8.4/en/create-trigger.html](https://dev.mysql.com/doc/refman/8.4/en/create-trigger.html).

## Related articles

* [Creating and executing MySQL stored functions and procedures](/docs/mysql-stored-functions-and-procedures)

* [MySQL views](/docs/mysql-views)

* [Connecting to MySQL from the command line](/docs/connect-to-mysql-from-the-command-line)

* [Connecting to MySQL using PHP](/docs/connect-to-mysql-using-php)

* [Managing MySQL databases, users, and tables from the command line](/docs/managing-mysql-databases-and-users-from-the-command-line)