Question

Advanced Mysql - Triggers Programming

Answer 1

Advanced Mysql - Triggers Programming

A trigger (Triggers) is an object within the database that executes an action when an operation or event has occurred in the database.
Example when an entry ticket is sold, we discount an available locality.

The generic code is

CREATE TRIGGER name trigger

{BEFORE | DESPUES DE }
// Will be executed before or after the event
{INSERT | UPDATE | DELETE}
/ / action or event triggered by the trigger
ON tablename
// name of the table that affected the event
FOR EACH ROW

sql sentence that will be executed

We created the example of ticket sales for an event or product in stock. To test this you can use phpmyadminn or any software that supports stored procedures and triggers.

We create the database

CREATE DATABASE `sales`

We create 2 tables

The table to store the tickets sold

CREATE TABLE `tickets` (
`id` int (10) NOT NULL AUTO_INCREMENT,
`idevento` int (11) NOT NULL,
`nroticket` int (11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1;

The table to store the availability of tickets for a specific event

CREATE TABLE IF NOT EXISTS `stock` (
`idevento` int (10) NOT NULL,
`stockactual` int (10) NOT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1;


CREATE TABLE `events` (
`idevento` int (10) NOT NULL AUTO_INCREMENT,
`event` int (11) varchar (200),
`date` date NOT NULL,
PRIMARY KEY (`idevento`)
) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1;


We create the trigger as an sql query, in this case we use the free software HEIDISQL, the sentence NEW.nombre_column
a indicates which field of the trigger operation we are going to use in the execution in this case idevento, if there were more I always use them with NEW in front,

CREATE TRIGGER `update_stock` AFTER INSERT ON` tickets`
FOR EACH
ROW
UPDATE stock SET stockactual = stockactual -1 WHERE idevento = NEW.idevento

: We execute the SQL statement and we see that the trigger has indeed been created :

As an example we insert in the stock table of 500 tickets available for an event, here there will be no response from the trigger since we create it to run if an insertion occurs in the tickets table.

INSERT INTO `sales`.`stock` (` idevento`, `stockactual`) VALUES ('1', '500');

Now let's try the magic of the triggers

Suppose we sell ticket number 100 for event 1 and insert the sale in the database tickets

INSERT INTO tickets (idevento, nroticket) VALUES ('1', '100');

Observe what happened in the stock table and we will see that the stock for this event now has 499 tickets available, because the insertion in the ticket table triggered and I executed the tigger updates_stock .

The use of this methodology allows transparency of execution both to the user and the programmer, knowing that an action will be executed if another triggers it and thus avoid routine tasks such as updating a stock in case of sales, imagine this if the sale is a market with thousands of product, we see how with a few lines of code we solve a big problem.

Another example could be to remove a product from the warehouse and remove the stock


CREATE TRIGGER `Low_products` AFTER DELETE ON` products`
FOR EACH
DELETE FROM stock WHERE idproduct = NEW.idproduct
ROW


The possibilities are endless and you can go deep into more complex situations, triggers or triggers are a great complement to stored procedures

---