MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Function Overview

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

What is MySQL Trigger?

A MySQL trigger is a stored database object that is automatically executed or fired when certain conditions are met. It contains a set of SQL statements that are invoked upon a certain event happening in a database. The trigger is associated with a table and is activated when an INSERT, UPDATE, or DELETE statement is issued against this table.

Here's a more detailed breakdown:

Event: This is the specific database operation that activates the trigger. In MySQL, a trigger is activated by an INSERT, UPDATE, or DELETE event on a particular table where the trigger is defined.
Trigger Time: This defines when the trigger should be activated. There are two options: BEFORE and AFTER. BEFORE triggers are activated before the triggering event (e.g., INSERT, UPDATE, DELETE) is executed. AFTER triggers are activated after the triggering event.
Triggering Statement or SQL Statement: This is the SQL code inside the trigger that will be run when the trigger is activated.

Here is an example of a MySQL trigger:

CREATE TRIGGER before_employee_update 
BEFORE UPDATE 
ON employees 
FOR EACH ROW 
BEGIN 
    INSERT INTO employees_audit SET action = 'update', employeeNumber = OLD.employeeNumber, changedat = NOW(); 
END;

In this example, before_employee_update is a trigger that is set to be invoked BEFORE UPDATE on the employees table. When an UPDATE statement is executed on the employees table, before the actual update happens, the trigger inserts a new row into the employees_audit table, logging the action.

Triggers can be used for various tasks such as enforcing business rules, validating input data, or keeping an audit trail.

Creating triggers in MySQL:
Triggers are defined using the CREATE TRIGGER statement. The basic syntax is as follows:
```
DELIMITER //
CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE] ON table_name
FOR EACH ROW
BEGIN
    -- Trigger body: SQL statements to be executed
END;
//
DELIMITER ;
```
For example, let's create a trigger that automatically updates a timestamp column whenever a row is updated in a table:
```
DELIMITER //
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END;
//
DELIMITER ;
```
In this example, the trigger is named update_timestamp, it is set to fire before an update on your_table, and it updates the updated_at column with the current timestamp.
How to use triggers in MySQL:
Once a trigger is created, it will automatically execute when the specified event (e.g., BEFORE UPDATE) occurs on the associated table. Triggers can be useful for enforcing data integrity, logging changes, or automating certain tasks.
To use a trigger, you simply perform the triggering event on the associated table. For example, if you update a row in the table specified in the trigger, the trigger will automatically execute.
MySQL trigger examples:
Here's an example of a trigger that logs changes to a separate audit table:
```
DELIMITER //
CREATE TRIGGER log_changes
AFTER UPDATE ON your_table
FOR EACH ROW
BEGIN
    INSERT INTO audit_table (old_value, new_value, change_timestamp)
    VALUES (OLD.column1, NEW.column1, NOW());
END;
//
DELIMITER ;
```
In this example, the trigger log_changes is set to execute after an update on your_table. It logs the old and new values of column1 into an audit_table along with the timestamp.
MySQL trigger events and actions:
Triggers can be associated with BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, or AFTER DELETE events. The trigger body contains the SQL statements to be executed in response to the specified event.
For instance, a trigger before an insert event:
```
DELIMITER //
CREATE TRIGGER before_insert_example
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    -- Trigger body: SQL statements for BEFORE INSERT
END;
//
DELIMITER ;
```
Managing and maintaining triggers in MySQL:
Triggers can be managed and maintained using the following commands:
- To view existing triggers on a database:
```
SHOW TRIGGERS;
```
- To drop (delete) a trigger:
```
DROP TRIGGER IF EXISTS your_trigger;
```
- To modify a trigger, you must drop and recreate it:
```
DROP TRIGGER IF EXISTS your_trigger;
DELIMITER //
CREATE TRIGGER your_trigger
-- Rest of the trigger definition
//
DELIMITER ;
```
Triggers should be used judiciously, as they can impact performance and add complexity to the database schema.