An Introduction to Triggers in MySQL 5.0

Triggers are a new addition to MySQL 5.0 and bring much needed, enterprise level, functionality to the MySQL database. This added functionality allows it to compete with other databases such as Oracle, DB2 and MSSQL Server and make it a more attractive solution.

For programmers who have not used triggers they are named database objects/stored programs that are associated with a table and are activated when a certain events occur on that table. A trigger can be defined to activate BEFORE or AFTER an INSERT, DELETE or UPDATE statement executes on the table it is assigned to.

Creating a MySQL Triggers:
Before you create a TRIGGER you need to change the delimiter to $$. This is required because MySQL will stop processing code when it reads a semicolon. Changing the delimiter ensures that MySQL processes the entire block of code that defines your trigger. At the end of your trigger you should change the delimiter back to ;.

The following example shows an AFTER INSERT trigger example on the table orders and deletes entries from the temp_orders table when an order is finalized and gets moved to orders

DELIMITER $$

DROP TRIGGER IF EXISTS `dbName`.`orders_ai`$$

CREATE TRIGGER `dbName`.`orders_ai` AFTER INSERT on `orders`
FOR EACH ROW 
BEGIN
   DELETE FROM temp_orders WHERE order_id = NEW.order_id;
END;

$$

DELIMITER ; 

Updating a MySQL Triggers:
To update your trigger you have to simply make changes to your code and recompile it. That is why you should make it standard practice to add the DROP TRIGGER IF EXISTS line before you create your trigger as in the example above. That line will delete the trigger if it exists in the database and recreates the trigger.

Deleting a MySQL Triggers:
Deleting a trigger is simple

DROP TRIGGER `dbName`.`orders_ai`;

 

Related Articles

• MySQL Stored Programs - Functions
• MySQL Stored Programs - Cursors

 

blog comments powered by Disqus

Lijit Search