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
BEFORE the sql instruction, which can be an
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;
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;