Triggers in MySQL

MySQL triggers are procedures that fire when certain events occur, such as inserting, deleting, changing a string.

An example of viewing triggers:

SHOW TRIGGERS\G;

Example of removing a trigger:

DROP TRIGGER name;

Triggers can be executed both before and after the execution of the request (BEFORE / AFTER).

Trigger Syntax:

CREATE
    TRIGGER `name` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `database`.`table`
    FOR EACH ROW BEGIN
		-- trigger body
    END;​

An example of creating a trigger:

DELIMITER //
CREATE TRIGGER next_contract_id BEFORE INSERT ON users_pi
FOR EACH ROW BEGIN
   IF NEW.contract_id = '' THEN
    SET NEW.contract_id=NEW.uid;
  END IF;
 
  IF NEW.contract_date = '' or NEW.contract_date = '0000-00-00' THEN
    SET NEW.contract_date=curdate();
  END IF;
 
END; 
//
DELIMITER ;
Did my article help you? How about buying me a cup of coffee as an encouragement? Buy me a coffe.

Leave a comment

Leave a Reply