What is TRIGGERS and how it can be used in MySQL?
In Mysql, a trigger is a database object that is directly associated with a table. It will be activated when a defined action is executed for the table.
It can be performed when you run one of the following MySQL like INSERT, UPDATE and DELETE occurred in a table. Its activation time can be BEFORE or AFTER.
Types of Triggers in MySQL
- Before Insert: This is done before inserting data into the table.
- After Insert: This is after data has been entered into the table.
- Before update: This is activated prior to the updating of the data in the table.
- After update: This function is activated upon the updating of the table's data.
- Before delete: This is done before any data are removed from the table.
- After delete: This function is activated when the data has been deleted from the table.
What is the purpose of triggers in MySQL?
- It is easy to set up triggers.
- Triggers allow us to validate data before it is inserted or updated.
- Triggers allow us to keep track of records, such as audit trails for tables.
- Triggers increase the performance of SQL queries as it doesn't need to compile every time the query executes.
- Triggers can be used to reduce client-side code, which saves time and effort.
- It allows us to scale our applications across multiple platforms.
- SQL triggers are an alternative method to verify the integrity of data.
- Triggers are an alternative method to perform the task.
Syntax
CREATE TRIGGER trigger_name
(AFTER | BEFORE) (INSERT | UPDATE | DELETE)
ON table_name FOR EACH ROW
BEGIN
--trigger code // You can write here your code
END;
Example
delimiter //
CREATE TRIGGER age_check BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;
delimiter ;