MySql Triggers

 A trigger in MySQL is a set of SQL statements that reside in a system catalog. It is a special type of stored procedure that is invoked aut...

 A trigger in MySQL is a set of SQL statements that reside in a system catalog. It is a special type of stored procedure that is invoked automatically in response to an event. Each trigger is associated with a table, which is activated on any DML statement such as INSERT, UPDATE, or DELETE.

Types of Triggers in MySQL?

We can define the maximum six types of actions or events in the form of triggers:

1. Before Insert
   : It is activated before the insertion of data into the table.
2. After Insert
   : It is activated after the insertion of data into the table.
3. Before Update
   : It is activated before the update of data in the table.
4. After Update
   : It is activated after the update of the data in the table.
5. Before Delete
   : It is activated before the data is removed from the table.
6. After Delete
   : It is activated after the deletion of data from the table.

1. CREATE TABLE employee(  
2.     name varchar(45) NOT NULL,    
3.     occupation varchar(35) NOT NULL,    
4.     working_date date,  
5.     working_hours varchar(10)  
6. );  
   
   1. INSERT INTO employee VALUES    
   2. ('Robin', 'Scientist', '2020-10-04', 12),  
   3. ('Warner', 'Engineer', '2020-10-04', 10),  
   4. ('Peter', 'Actor', '2020-10-04', 13),  
   5. ('Marco', 'Doctor', '2020-10-04', 14),  
   6. ('Brayden', 'Teacher', '2020-10-04', 12),  
   7. ('Antonio', 'Business', '2020-10-04', 11);  

   Next, execute the SELECT statement to verify the inserted record:

1. mysql> DELIMITER //  
2. mysql> Create Trigger before_insert_empworkinghours   
3. BEFORE INSERT ON employee FOR EACH ROW  
4. BEGIN  
5. IF NEW.working_hours < 0 THEN SET NEW.working_hours = 0;  
6. END IF;  
7. END //  

8. Now, we can use the following statements to invoke this trigger:

   1. mysql> INSERT INTO employee VALUES    
   2. ('Markus', 'Former', '2020-10-08', 14);  
   3.   
   4. mysql> INSERT INTO employee VALUES    
   5. ('Alexander', 'Actor', '2020-10-012', -13);