A trigger is a stored program that gets invoked automatically whenever any event such as insertion, update, or deletion occurs in a table.

For instance, whenever we add a new row or delete a row, we can define a trigger that gets triggered automatically through which we can be assured of the proper happenings of those events. Each trigger is associated with a table.

The trigger is a special procedure but not a stored procedure. The key difference between both is that a stored procedure needs to be called explicitly while the special procedure(trigger) gets called automatically when the event linked to it happens.

Use of triggers:

• Data can be validated even before they are inserted or updated with the help of triggers.
• A log of records can be kept using triggers.
• Triggers provide an alternative way to check the integrity of data.
• Triggers also provide an alternative way to run the scheduled task.
• The performance of SQL queries can be improved with triggers because it does not need to compile each time the query is executed.

Limitations of triggers:

• There will be an increase in the overhead of the database server with the usage of triggers.
• Users can not troubleshoot what happens in the database layer as they get invoked automatically and invisibly.

How to create triggers:

CREATE TRIGGER trigger_name    

    (AFTER | BEFORE) (INSERT | UPDATE | DELETE)  

         ON table_name FOR EACH ROW    

         BEGIN    

        -declarations of variables    

        -code for the trigger    

        END;

Example: 

Step1: Creating a table named person

CREATE TABLE person(  

    name varchar(45) NOT NULL,    

    occupation varchar(35) NOT NULL,    

    date_of_working date,  

    working_hours varchar(10)  

);

Step2. Code for trigger creation

mysql> DELIMITER //  

mysql> Create Trigger before_insertion   

BEFORE INSERT ON employee FOR EACH ROW  

BEGIN  

IF NEW.working_hours < 0 THEN SET NEW.working_hours = 0;  

END IF;  

END //

Step3: Inserting values into the table named person

INSERT INTO person VALUES    

('Rahul', 'Astronaut', '2022-03-03', 12),  

('Raju', 'Engineer', '2022-03-03', 10),  

('Ravi', 'Painter', '2022-04-04', 13),  

('Rajesh', 'Doctor', '2022-04-04', 14)

Here, we are changing the delimiter to // so that it will not be terminated for the default termination character(;). With the help of this trigger, conditions like entering negative

values can be checked and acted upon by replacing them with values like 0 or any other value.

Example2:

Step1: First create a table named test_trigger as follows:

CREATE TABLE test_trigger(message VARCHAR(100));

Step2: Creating a table named person

CREATE TABLE employee(  

    name varchar(45) NOT NULL,    

    occupation varchar(35) NOT NULL,    

    sex varchar(5)

);

Step3: Create the trigger

mysql> DELIMITER //  

mysql> Create Trigger my_trigger

BEFORE INSERT ON employee FOR EACH ROW  

BEGIN  

IF NEW.sex = "M" THEN 

   INSERT INTO test_trigger VALUES("Added male employee");  

ELSE

   INSERT INTO test_trigger VALUES("Added female employee");  

END IF;  

END //

Step4. Inserting values into a table named person

INSERT INTO employee VALUES    

('Rahul', 'Astronaut', 'M'),  

('Raju', 'Engineer', 'F')

The above trigger adds the value based on what the user inserts into the gender column of the employee table. If we execute the “SELECT * FROM test_trigger” it will show the message column with the corresponding values depending on the INSERT operation on the employee table

To check all the triggers available in a database and associated with a table, the following statement works:

mysql> SHOW TRIGGERS FROM mydb WHERE table = 'person';

To drop a particular trigger, the following statement works:

mysql> DROP TRIGGER IF EXISTS mydb.before_insertion;

In a similar fashion, triggers for conditions like before_updation, after_updation, before_deletion, and after_deletion can be created and used.