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 ())
Consult all available triggers in the database Show triggers
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;
