Types of Triggers:

BEFORE Triggers: These triggers execute before the triggering event (e.g., BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE).

AFTER Triggers: These triggers execute after the triggering event (e.g., AFTER INSERT, AFTER UPDATE, AFTER DELETE).

Writing Triggers:

Triggers are defined using SQL and consist of three main parts: the starting condition, the visual event, and the user behavior.

The trigger event indicates when the trigger should run (e.g BEFORE INSERT, AFTER UPDATE).

The trigger condition (optional) specifies a condition that must be met for the trigger to execute.

The trigger action defines the SQL statements to execute when the trigger fires.

Example of Creating a Trigger:

Let's say you want to create a trigger that automatically updates a "last_modified" timestamp column whenever a row in the "employees" table is updated:

                                  
DELIMITER //
CREATE TRIGGER update_last_modified
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    SET NEW.last_modified = NOW();
END;
//
DELIMITER ;
                                  
                                

In this example:

• BEFORE 'UPDATE ON employees' declares that the trigger should execute before any 'UPDATE' of the employees table.
• FOR EACH ROW indicates that the trigger should be invoked for each row affected by the UPDATE operation.
• SET NEW.last_modified = NOW(); is the trigger action, which sets the "last_modified" column to the current timestamp (NOW() function) for the row being updated.

Managing Triggers:

Prompts can be created, edited and deleted with the help of a popular database language- SQL- usage that is similar to other database objects

Specify CREATE TRIGGER, ALTER TRIGGER and DROP TRIGGER statements to maintain triggers in MySQL.

Example of Dropping a Trigger:

To drop the trigger created in the previous example:

                                  
                                  DROP TRIGGER IF EXISTS update_last_modified;