MySQL Triggers are a set of SQL statements. It is a stored procedure that is invoked automatically in response to any event. Each Trigger is activated on any DML statement such as INSERT, UPDATE, DELETE, SELECT. Each Trigger is associated with the database table.
A trigger seems to be the same as a procedure, the main difference is that trigger is called automatically when any data modification is made against the table. Triggers cannot be called directly that’s why they are called special procedures. In contrast, stored procedures must be called explicitly.
Triggers are of 2 types according to the SQL standard.
- Row Level Triggers
- Statement Level Triggers
Row Level Trigger: A row Level trigger is a trigger that is activated for each row by a triggering statement such as insert, update or delete. For example, if insertion, deletion or updation is made in a table then the triggers are called automatically upon the rows affected by the respective statement.
Statement Level Trigger: A statement Level trigger is a trigger that is fired once for each event that occurs on a table regardless of how many rows are inserted, updated, or deleted.
We need triggers for the following reasons.
- Triggers help us to validate data even before it is inserted.
- An Alternate way is provided by triggers to run the scheduled task.
- Triggers increase the performance of SQL queries because it does not need to be compiled each time the query is executed.
- Triggers save time and effort as client-side code is reduced.
- Triggers also help to scale our application across different platforms.
- Triggers are easy to maintain.
Limitations of using Triggers in MySql
- MySQL triggers do not allow the use of all validations; they only provide extended validations. For example, we can use the NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints for simple validations.
- A Client application is not aware of the execution of triggers. Therefore, it is not easy to recognize what happens in the database layer.
- Triggers may increase the overall cost of the database server.
Types of triggers in MYSQL
- Before Insert: Trigger is activated before the insertion of data into the table.
- After Insert: Trigger is activated after the insertion of data into the table.
- Before Update: Trigger is activated before the data update into the table.
- After Update: Trigger is activated after the data update into the table.
- Before Delete: Trigger is activated before the data is removed from the table.
- After Delete: Trigger is activated after the data deletion from the table.
Naming Conventions
A unique name is used for each trigger associated with each table.
Following are the instructions to name the trigger.
(BEFORE | AFTER) table_name (INSERT | UPDATE | DELETE)
Trigger Activation Time : Before/After
Trigger Event : Insert/Update/Delete
Statement to create the trigger:
CREATE TRIGGER trigger_name
(AFTER | BEFORE) (INSERT | UPDATE | DELETE)
ON table_name FOR EACH ROW
BEGIN
--variable declarations
--trigger code
END;
Let’s Take an example to understand the Triggers
Now, we will create a Trigger that makes modifications to the employee table. Let’s create a new table employee by executing the below statement:
CREATE TABLE employee(
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
working_date date,
working_hours varchar(10)
);
Next, execute the below statement to fill the records into the employee table:
INSERT INTO employee VALUES
('Sunil', 'Scientist', '2021-10-04', 12),
('Rahul', 'Engineer', '2021-10-04', 10),
('Raman', 'Teacher', '2021-10-04', 13),
('Mandeep', 'Doctor', '2021-10-04', 14)
Next, execute the SELECT statement to verify the inserted record:
| name | occupation | working_date | working_hours |
|---|---|---|---|
| Sunil | Scientist | 2021-10-04 | 12 |
| Rahul | Engineer | 2021-10-04 | 10 |
| Raman | Teacher | 2021-10-04 | 13 |
| Mandeep | Doctor | 2021-10-04 | 14 |
Next, we will create a BEFORE INSERT trigger. This trigger is automatically invoked, setting the working_hours to 0 if someone tries to insert a negative value for working_hours.
Now, we can use the following statements to invoke this trigger:
mysql> INSERT INTO employee VALUES
('Rajan', 'Former', '2021-10-08', 14);
mysql> INSERT INTO employee VALUES
('Atinder', 'Actor', '2021-10-12', -13);
Now run SELECT * FROM emp;
| name | occupation | working_date | working_hours |
|---|---|---|---|
| Sunil | Scientist | 2021-10-04 | 12 |
| Rahul | Engineer | 2021-10-04 | 10 |
| Raman | Teacher | 2021-10-04 | 13 |
| Mandeep | Doctor | 2021-10-04 | 14 |
| Rajan | Former | 2021-10-08 | 14 |
| Atinder | Actor | 2021-10-12 | 0 |
Note: Working hours inserted as 0 for the name: Atinder
Knowledge of .NET is quite rewarding in the IT industry. If you have got some skills in the .NET framework then a .NET Certification Exams from StudySection can prove to be a good attachment with your resume. You can go for a foundation level certificate as well as an advanced level certificate in the .NET framework.
