Nuestra base de conocimientos sólo está disponible actualmente en inglés. Estamos en el proceso de traducir estas páginas y las publicaremos cuando estén disponibles.
Knowledge Base
How to create and use MySQL triggers
This article describes how to create and use MySQL triggers on your Webhost.Berlin 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 Webhost.Berlin 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;
You can run the previous SQL command (and the following SQL commands) from the command line using the MySQL tool, or in your web browser using phpMyAdmin.
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 ;
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.
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.
More Information
- For more information about MySQL triggers, please visit http://dev.mysql.com/doc/refman/5.0/en/triggers.html.
- For more information about the CREATE TRIGGER statement, please visit http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html.
