Introduction to MySQL Triggers

A trigger is a named database object that is associated with table.   

MySQL triggers is a set of actions that are run automatically which has specified set of operations (INSERT, UPDATE, DELETE statements). All these operations are performed on database table.

You can use the following statements in triggers which are used in MySQL procedures.

• Compound statements (BEGIN / END)

• Variable declaration (DECLARE) and assignment (SET)

• Flow-of-control statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE)

• Condition declarations

• Handler declarations 

trigger_name: All triggers must have unique names within schema.

All triggers must have unique names within a    schema. Triggers in different schemas can have the same name.

trigger_time: trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified.

trigger_event: trigger_event indicates the kind of operation that activates the trigger. These trigger_event values are permitted:

• The trigger activates whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements. 
• The trigger activates whenever a row is modified; for example, through UPDATE statements.
• The trigger activates whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE. Dropping a partition does not activate DELETE triggers, either.

tbl_name : The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view. 

trigger_body: trigger_body is the statement to execute when the trigger activates. To execute multiple statements, use the BEGIN ... END compound statement construct. This also enables you to use the same statements that are permissible within stored routines. 

Simple example of trigger:

CREATE TRIGGER ins_sum 
BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;

In the above example, there is new keyword 'NEW' which is a MySQL extension to triggers. There is two MySQL extension to triggers 'OLD' and 'NEW'. OLD and NEW are not case sensitive.

• Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger
• In an INSERT trigger, only NEW.col_name can be used.
• In a UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.
• In a DELETE trigger, only OLD.col_name can be used; there is no new row.

Row example:  
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `stagingxpo1`.`test` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `stagingxpo1`.`<Table Name>`
    FOR EACH ROW BEGIN

    END$$

DELIMITER ; 

Example AFTER INSERT:

DELIMITER $$
USE `hr`
$$
CREATE DEFINER=`root`@`127.0.0.1` TRIGGER `hr`.`emp_details_AINS` 
AFTER INSERT ON `hr`.`emp_details`
FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN 
INSERT INTO log_emp_details 
VALUES(NEW.employee_id, NEW.salary, NOW());
END$$

description

hr -> database name 

emp_details_AINS -> trigger name

this code will add new employee id salary and timestamp to

log_emp_details table after inserting record to emp_details.

Example BEFORE INSERT: 

USE `hr`;
DELIMITER $$
CREATE TRIGGER `emp_details_BINS` 
BEFORE INSERT ON emp_details FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above 

this one
BEGIN
SET NEW.FIRST_NAME = TRIM(NEW.FIRST_NAME);
SET NEW.LAST_NAME = TRIM(NEW.LAST_NAME);
SET NEW.JOB_ID = UPPER(NEW.JOB_ID);

END;
$$

this will trim first_name new value, trim last_name new value and all 

upper job_id new value before insert. 

Example AFTER UPDATE

-- Full Trigger
DDL Statements
-- Note: Only CREATE TRIGGER statements are allowed
DELIMITER $$
USE `test` $$
CREATE DEFINER=`root`@`127.0.0.1`TRIGGER `test`.`student_mast_AUPD`
AFTER UPDATE ON `test`.`student_mast`
FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN
INSERT into stu_log VALUES (user(), CONCAT('Update Student Record ',
         OLD.NAME,' Previous Class :',OLD.ST_CLASS,' Present Class',
         NEW.st_class));
END
$$

this will add row to stu_log table after update student_mast record.

Example BEFORE UPDATE

USE `test`;
DELIMITER $$
CREATE TRIGGER `student_marks_BUPD` BEFORE UPDATE 
ON student_marks FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN 
SET NEW.TOTAL = NEW.SUB1 + NEW.SUB2 + NEW.SUB3 + NEW.SUB4 + NEW.SUB5; 
SET NEW.PER_MARKS = NEW.TOTAL/5;
IF NEW.PER_MARKS >=90 THEN
  SET NEW.GRADE = 'EXCELLENT';
ELSEIF NEW.PER_MARKS>=75 AND NEW.PER_MARKS<90 THEN
  SET NEW.GRADE = 'VERY GOOD';
ELSEIF NEW.PER_MARKS>=60 AND NEW.PER_MARKS<75 THEN
  SET NEW.GRADE = 'GOOD';
ELSEIF NEW.PER_MARKS>=40 AND NEW.PER_MARKS<60 THEN
  SET NEW.GRADE = 'AVERAGE';
ELSE

  SET NEW.GRADE = 'NOT PROMOTED';
END IF;
END;
$$

This will set GRADE for every new record before it insert to db table.

Example AFTER DELETE

USE `test`;
DELIMITER $$
CREATE TRIGGER `student_mast_ADEL` AFTER DELETE ON student_mast FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN
INSERT into stu_log VALUES (user(), CONCAT('Update Student Record ',
         OLD.NAME,' Clas :',OLD.ST_CLASS, '-> Deleted on ', NOW()));
END;
$$ 
 
delete has only after delete triggers.

REFERENCE: https://www.w3resource.com/mysql/mysql-triggers.php

Introduction to MySQL Triggers