Triggers and Events in MySQL

Triggers: Triggers are special routines that automatically execute in response to specific changes in a database table, such as `INSERT`, `UPDATE`, or `DELETE` operations. They help enforce business rules and maintain data integrity by performing automated actions when certain conditions are met.

– Events: Events are scheduled tasks that run automatically at specified intervals or times. They are used to automate repetitive tasks like data cleanup or periodic updates. Events are created using the `CREATE EVENT` statement and managed with the `ALTER EVENT` and `DROP EVENT` statements.

Both triggers and events enhance database functionality by automating processes and maintaining data consistency.

Sample codes for triggers and events in MySQL:

These examples illustrate how triggers and events can automate database operations and maintain data integrity

1. Trigger Example

Suppose you have a table `employees` and want to automatically update the `last_modified` timestamp whenever a record is updated:

sql

— Create a trigger

DELIMITER //

CREATE TRIGGER update_employee_timestamp

BEFORE UPDATE ON employees

FOR EACH ROW

BEGIN

    SET NEW.last_modified = NOW();

END //

DELIMITER ;

Explanation:

– `BEFORE UPDATE ON employees`: Specifies that the trigger should fire before an update on the `employees` table.

– `FOR EACH ROW`: Indicates the trigger operates on each row being updated.

– `SET NEW.last_modified = NOW();`: Updates the `last_modified` column with the current timestamp.

2. Event Example

Suppose you want to delete records from a `logs` table that are older than 30 days, and you want this to happen daily:

“`sql

— Create an event

DELIMITER //

CREATE EVENT delete_old_logs

ON SCHEDULE EVERY 1 DAY

DO

BEGIN

    DELETE FROM logs WHERE log_date < NOW() – INTERVAL 30 DAY;

END //

DELIMITER ;

“`

Explanation:

– `ON SCHEDULE EVERY 1 DAY`: Sets the event to run every day.

– `DELETE FROM logs WHERE log_date < NOW() – INTERVAL 30 DAY;`: Deletes records from the `logs` table where the `log_date` is older than 30 days.