Sunday, April 3, 2022

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 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   
  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);  
    4. mysql> INSERT INTO employee VALUES    
    5. ('Alexander''Actor''2020-10-012', -13);  


Post a Comment