MySQL Triggers

Table of Contents

• What are Triggers in MySQL?
• Create a Trigger
• Trigger Invocation
• Additional Example
• Drop a Trigger

Triggers are routines associated to database tables. These routines are then invoked when a record is added, updated or deleted to those tables.

With Triggers you can perform any sql operation on any table of your database. This is useful, for instance, when you have redundant fields such as accumulators or counters that you want to be updated automatically by the database, instead of having to code it in your application.

A Trigger is created with the CREATE TRIGGER instruction as shown in the following code:

delimiter //

create trigger upd_prod_status before update on products
for each row
begin
    if new.quantity <= 0 then
        set new.status = 'out-of-stock';
    elseif new.quantity <= 5 then
        set new.status = 'low-stock';
    elseif new.quantity > 5 then
        set new.status = 'in-stock';
    end if;
end //

delimiter ;

We specify if the trigger will be invoked AFTER or BEFORE the sql instruction, which can be an INSERT, UPDATE, or DELETE.

Inside the BEGIN and END statements, we will put our code.

With the Trigger created in the code shown above, once we perform a sql update operation such as:

update `products` set `quantity` = '95' where `products`.`id` = 1; 

We will see the additional change done on the table:

// When querying the table we will find the `status` field was also automatically modified
select id, status from `products` where id = 1;

    

        
id
        
status
    
    

        
1
        
in-stock
 
    

In the example shown above we have updated a field from the same table where the trigger was defined.

In the next example below, we are affecting other tables, to illustrate that the triggers are not restricted to their associated table, but that indeed can perform any sql operation in other tables of the database.

delimiter //

create trigger upd_prod_tables after insert on products
for each row
begin
    insert into product_tags (product_id, tag) values (new.id, 'new-product');
    update product_categories set num_products = num_products + 1 where category_id = new.category_id;
end //

delimiter ;

When you no longer need a Trigger you can delete it with the DROP TRIGGER instruction.

drop trigger upd_prod_status;

drop trigger upd_prod_tables;

Related Posts