+4 votes
230 views
Mysql triggers or trigger - Defining events and moments

in Databases by (552k points)
reopened | 230 views

1 Answer

+5 votes
Best answer

The statement that creates a trigger is

 CREATE TRIGGER name_trigger moment event ON table ON EACH ROW 


Moment
It can be AFTER or BEFORE after or before an sql event occurs such as create a record, delete, select, search, modify, etc.

Event
We call event to any of the operations sql can be INSERT, UPDATE or DELETE. Since the triggers can not be selected (Select * from ...) this operation is performed from stored procedures and not from triggers.


Sentences and general commands to manage triggers or triggers
We create a trigger for sales audit of a car agency, every time an operator or seller modifies the data of a client, automatically in a table of audits_client we will indicate the date and time, who made the change and which is the client that was modified

 CREATE TRIGGER trigger_auditoria_clients AFTER UPDATE ON clients FOR EACH ROW INSERT INTO customer_assessment (customer_id, modified_by, date) VALUES (OLD.idcliente, NEW.idoperador, NOW ()) 


image


Consult all available triggers in the database
 Show triggers 


image


In this case we create auditing clients and we see in different columns its functionality, an update event in the clients table that will do an insertion and its administrator user is root

Delete a trigger from the database
 DROP TRIGGER nombretrigger 

How to call stored procedures in a trigger
We will analyze an example for a database of products and that when generating a sale a seller must also generate the commission that he / she earns, then the sales commission should be updated after inserting or generating the new sale or it could also be generated after an invoice, according to the business model to be developed, but it is sufficient for the example to be understood.
We create a stored procedure that will calculate the commission according to the quantity of a product sold.

 DELIMITER $$ CREATE PROCEDURE pa_commission` (IN p_printer INT, IN p_product INT, IN INT amount) BEGIN DECLARE totalcomision INT DEFAULT 0; Select commission from products where idproduct = p_idproduct; totalcomision = commission * p_quantity insert into commissions (seller, commission) values ​​(idvendedor, idproducto, comision, totalcomision); END $$ DELIMITER $$ 

Now we create the new trigger or trigger. This trigger will be executed after a sale is inserted and taking the data of this or the sales detail.
 CREATE TRIGGER new sales AFTER INSERT on sales FOR EACH ROW BEGIN CALL pa_commissions (new.seller, new.idproduct, new.quantity); END $$ 

We can see that the stored procedure is called to be able to consult the product data, the commission and thus be able to automatically calculate the commission amount.

We could also make it more complex by letting you know if the commission and the sale already exist then modify it and not insert it again.

For this we should also recognize the sales number or invoice, then we look for if the product already exists in the sales and sales number, implies that it already exists and is commissioned, therefore we will modify it and not insert it as a new sale.

 select count (idproduct)> 0 into already exists from commissions where idvendedor = idvendedor and idventa = idventa; if it already exists then UPDATE commission set comision = totalcomisión where idvendedor = idvendedor and idventa = idventa; else insert into commissions (seller, commission) values ​​(idvendedor, idproducto, comision, totalcomision); end if; 

by (3.5m points)
edited

Related questions

+5 votes
1 answer
asked Jun 24, 2019 in Databases by backtothefuture (552k points) | 263 views
+5 votes
1 answer
asked Sep 30, 2019 in Databases by backtothefuture (552k points) | 1.7k views
+5 votes
1 answer
+4 votes
1 answer
asked Aug 21, 2019 in Databases by backtothefuture (552k points) | 366 views
+5 votes
1 answer
Sponsored articles cost $40 per post. You can contact us via Feedback

Most popular questions within the last 30 days

10,659 questions
10,791 answers
510 comments
3 users