Triggers in MySQL
A trigger
is a stored program appealed automatically when an event such as insert, update,
or delete occurs in the associated table.
According
to SQL, standard triggers are classified into two types of row-level
triggers and statement-level triggers.
- A row-level trigger is activated when each
row is inserted, updated, or deleted. For example, if a table has
100 rows inserted, updated, or deleted, the trigger is automatically
invoked 100 times for the 100 rows affected.
- A statement-level trigger is executed once for
each transaction according to how many rows are inserted, updated, or
deleted.
MySQL
supports only row-level triggers. It doesn’t support statement-level triggers.
Advantages of triggers
- Check the integrity of data.
- Handle errors from the database
layer.
- Provide an alternative ways to run
scheduled tasks.
- Perform automatically scheduled
events according to before or after a
change in a table.
- Useful for auditing the data changes
in tables.
- Help to enforce business rules.
- Help to validate data before and after inserted or updated data in the table.
- Help to store a log of records for maintaining audit trails in
tables.
- Provide an alternative way to check the integrity of data.
- Increases the performance of SQL queries because it does not need
to compile each time the query is executed.
- Reduce the client-side code and saves time and effort.
- Help to measure applications across different platforms.
- Easy to maintain.
Disadvantages of triggers
- It only provides extended
validations, not all validations.
- Difficult to troubleshoot because
they execute automatically in the database, so not invisible to the client
applications.
- Increase the overhead of the MySQL
Server.
Create Trigger in MySQL
Basic
syntax:- CREATE TRIGGER statement:
CREATE TRIGGER
trigger_name
{BEFORE |
AFTER} {INSERT | UPDATE| DELETE }
ON table_name
FOR EACH ROW
trigger_body;
In this
syntax:
- Define the trigger
name after the
CREATE TRIGGERkeywords. - The trigger names must be unique within a database.
- Define trigger
action time as either
BEFOREorAFTEReach row is modified. - Define the operation that activates the trigger, on INSERT, UPDATE, or DELETE.
- Define the table name to which the
trigger belongs after the
ONkeyword. - Finally, Define the execute statement which triggers activities.
If to execute multiple statements, use
the BEGIN END compound statement.
Types of Triggers in MySQL
Six types
of actions or events in the form of triggers:
- Before Insert: It is activated before
the insertion of data into the table.
- After Insert: It is activated
after the insertion of data into the table.
- Before Update: It is activated
before the update of data in the table.
- After Update: It is activated
after the update of the data in the table.
- Before Delete: It is activated
before the data is removed from the table.
- After Delete: It is activated
after the deletion of data from the table.
Examples:-
- Create
a table
employeesand insert records.
CREATE TABLE employees_audit ( id INT AUTO_INCREMENT PRIMARY KEY,
employeeNumber INT NOT NULL, lastname VARCHAR(50) NOT NULL,
changedat DATETIME DEFAULT NULL, action VARCHAR(50) DEFAULT NULL );
Create a BEFORE UPDATE trigger on the employees table.
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
INSERT INTO employees_audit
SET action = 'update',
employeeNumber = OLD.employeeNumber,
lastname = OLD.lastname,
changedat = NOW();
Inside the body of the trigger, used the OLD keyword to access values of the columns employeeNumber and lastname of the row affected by the trigger.
Show all triggers in the current database by using the SHOW TRIGGERS statement:
SHOW TRIGGERS;
After
that, update a row in the employees table:
UPDATE employees SET lastName =
'Phan' WHERE employee number = 1056;
Finally,
query the employees audit table to check if the trigger was
fired by the UPDATE statement:
SELECT * FROM
employees_audit;
Output:-
====================================
MySQL DROP TRIGGER
Deletes a
trigger from the database.
Syntax:
DROP TRIGGER
trigger_name;
If a trigger does not exist without using the IF EXISTS clause, MySQL shows an error.
The DROP TRIGGER requires the TRIGGER privilege for the table associated
with the trigger.
Note:- if drop a table, MySQL will
automatically drop all triggers associated with the table.
=====================================================================
MySQL BEFORE INSERT Trigger
It is automatically
fired before an insert event occurs on the table.
Syntax:
CREATE TRIGGER
trigger_name
BEFORE INSERT
ON table_name FOR EACH ROW
trigger_body;
Example:-
Create a table called person_archive with name, age,
and time columns:
CREATE TABLE
person_archive ( name varchar(45), age int, time timestamp DEFAULT NOW());
Create a BEFORE INSERT Trigger
delimiter //
CREATE TRIGGER
person_bi BEFORE INSERT
ON person
FOR EACH ROW
IF NEW.age <
18 THEN
SIGNAL SQLSTATE
'50001' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //
delimiter ;
Inserting data activates the trigger and checks the value of age before
committing the information:
INSERT INTO
person VALUES ('John', 14);
The console
displays the descriptive error message. The data does not insert into the table
because of the failed trigger check:-
Error (50001):
Person must be older than 18.
====================================================
MySQL AFTER INSERT Trigger
Automatically invoked after an insert event occurs on the table.
Syntax:-
CREATE TRIGGER trigger_name
AFTER INSERT
ON table_name FOR EACH ROW
trigger_body
Example
First, create a table member:
CREATE TABLE members ( id INT AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(255), birthDate DATE, PRIMARY KEY (id) );
Second, create another table reminders that stores reminder messages to members.
CREATE TABLE reminders ( id INT AUTO_INCREMENT, memberId INT,
message VARCHAR(255) NOT NULL, PRIMARY KEY (id, memberId) );
AFTER INSERT trigger example
Creates an AFTER INSERT trigger that inserts a reminder into the reminders table if the birth date of the member is NULL.
DELIMITER $$
CREATE TRIGGER after_members_insert
AFTER INSERT
ON members FOR EACH ROW
BEGIN
IF NEW.birthDate IS NULL THEN
INSERT INTO reminders(memberId, message)
VALUES(new.id,CONCAT('Hi ', NEW.name, ', please update your date of birth.'));
END IF;
END$$
DELIMITER;
EXECUTION :-
INSERT INTO members(name, email, birthDate) VALUES ('John Doe', 'john.doe@example.com', NULL),
('Jane Doe', 'jane.doe@example.com','2000-01-01');
Query data from the members table: SELECT * FROM members;
Third, query data from reminders table: SELECT * FROM reminders;
members table. only the first row that has
a birth date value NULL, the trigger inserted only one row into
the reminders table.
====================================================================
MySQL BEFORE UPDATE Trigger
MySQL BEFORE UPDATE triggers are invoked automatically before
an update event occurs on the table associated with the triggers.
Syntax:
CREATE TRIGGER
trigger_name
BEFORE UPDATE
ON table_name
FOR EACH ROW
trigger_body
BEFORE UPDATE
Trigger Example
Created a table
named:- sales_info
CREATE TABLE sales_info ( id INT AUTO_INCREMENT, product VARCHAR(100) NOT NULL, quantity INT NOT NULL DEFAULT 0, fiscalYear SMALLINT NOT NULL,
CHECK (fiscalYear BETWEEN 2000 and 2050),
CHECK (quantity >=0), UNIQUE(product, fiscalYear), PRIMARY KEY(id) );
Insert records
into the sales_info table as follows:
INSERT INTO sales_info(product, quantity, fiscalYear) VALUES ('2003 Maruti Suzuki',110, 2020), ('2015 Avenger', 120,2020), ('2018 Honda Shine', 150,2020), ('2014 Apache', 150,2020);
Execute the SELECT statement :
CREATE
TRIGGER before_update_salesInfo .
This trigger is invoked automatically before an update event occurs in the
table.
DELIMITER $$
CREATE TRIGGER before_update_salesInfo
BEFORE UPDATE
ON sales_info FOR EACH ROW
BEGIN
DECLARE error_msg VARCHAR(255);
SET error_msg = ('The new quantity cannot be greater than 2 times the current quantity');
IF new.quantity > old.quantity * 2 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = error_msg;
END IF;
END $$
DELIMITER ;
Output:-
The trigger
produces an error message and stops the updation if we update the value in the
quantity column to a new value two times greater than the current value.
===================================================
MySQL AFTER
UPDATE Trigger
MySQL AFTER
UPDATE triggers is invoked automatically after an update event
occurs on the table associated with the triggers.
Syntax:-
CREATE TRIGGER
trigger_name
AFTER UPDATE
ON table_name
FOR EACH ROW
trigger_body
AFTER
UPDATE Trigger Example
Created a
table students for store the student's information:
CREATE TABLE students( id int NOT NULL AUTO_INCREMENT,name varchar(45) NOT NULL, class int NOT NULL, email_id varchar(65) NOT NULL, PRIMARY KEY (id));
Insert some
records:
INSERT INTO students (name, class, email_id) VALUES ('Stephen', 6, 'stephen@javatpoint.com'), ('Bob', 7, 'bob@javatpoint.com'),
('Steven', 8, 'steven@javatpoint.com'), ('Alexandar', 7, 'alexandar@javatpoint.com');
Execute
the SELECT query for display
records.
Create
another table students_log to store the updated information in
the selected user.
CREATE TABLE students_log( user varchar(45) NOT NULL, descreptions varchar(65) NOT NULL );
Create an
AFTER UPDATE trigger that
promotes all students in the next class (i.e., 6 will be 7, 7 will be 8, and so on).
Whenever an
update is performed on a single row in the "students" table, a new row will be inserted in
the "students_log" table because this table keeps
the current user
id and
a description regarding the current update.
Syntax trigger:
DELIMITER $$
CREATE TRIGGER after_update_studentsInfo
AFTER UPDATE
ON students FOR EACH ROW
BEGIN
INSERT into students_log VALUES (user(),
CONCAT('Update Student Record ', OLD.name, ' Previous Class :',
OLD.class, ' Present Class ', NEW.class));
END $$
DELIMITER ;
Execution:-
·
Update the "students"
table using the following statements:
UPDATE students SET class = class + 1;
·
Display data
from the students and students_log table.
Output:
==================================================
MySQL BEFORE
DELETE Trigger
Fired
automatically before a delete event
occurs in a table.
Syntax:-
CREATE TRIGGER
trigger_name
BEFORE DELETE
ON table_name FOR EACH ROW
trigger_body
BEFORE
DELETE Trigger Example
·
Create a
table salaries to store the salary information of an employee as follows:
CREATE TABLE salaries (emp_num INT PRIMARY KEY,valid_from DATE
NOT NULL, amount DEC(8 , 2 ) NOT NULL DEFAULT 0 );
·
Insert
records into this table :
INSERT INTO salaries (emp_num, valid_from, amount) VALUES
(102, '2020-01-10', 45000), (103, '2020-01-10', 65000), (105, '2020-01-10', 55000),
(107, '2020-01-10', 70000), (109, '2020-01-10', 40000);
·
Execute the
SELECT query from salaries.
Output:
·
Create
another table salary_archives for the information of deleted salary.
CREATE TABLE salary_archives ( id INT PRIMARY KEY AUTO_INCREMENT, emp_num INT, valid_from DATE NOT NULL, amount DEC(18 , 2 ) NOT NULL DEFAULT 0, deleted_time TIMESTAMP DEFAULT NOW());
·
Create a
BEFORE DELETE trigger before_delete_salaries
that inserts a new record into the salary_archives table before a row is
deleted from the salaries table.
DELIMITER $$
CREATE TRIGGER before_delete_salaries
BEFORE DELETE
ON salaries FOR EACH ROW
BEGIN
INSERT INTO salary_archives (emp_num, valid_from, amount)
VALUES(OLD. emp_num, OLD.valid_from, OLD.amount);
END$$
DELIMITER ;
·
Remove a row
from the salaries table as:
DELETE FROM salaries WHERE emp_num = 105;
·
Query data
from the salary_archives table to verify the above-created trigger is invoked
or not by using the select statement:
SELECT * FROM salary_archives;
Output:-
DELETE FROM salaries;
·
Show data
from the salary_archives table.
Output:-
The trigger shows
four times because the DELETE statement removed four records from the salaries
table.
=======================================================
MySQL AFTER
DELETE Trigger
Fired automatically
after a delete event occurs on the table.
Syntax:-
CREATE TRIGGER
trigger_name
AFTER DELETE
ON table_name FOR EACH ROW
trigger_body;
AFTER
DELETE Trigger Example
·
Created a
table named salaries to store the
salary information of an employee as follows:
CREATE TABLE salaries ( emp_num INT PRIMARY KEY, valid_from DATE
NOT NULL, amount DEC(8 , 2 ) NOT NULL DEFAULT 0 );
·
Insert records:
INSERT INTO salaries (emp_num, valid_from, amount) VALUES
(102, '2020-01-10', 45000), (103, '2020-01-10', 65000), (105, '2020-01-10', 55000),
(107, '2020-01-10', 70000), (109, '2020-01-10', 40000);
·
Execute the
SELECT query from salaries table.
Output:-
·
Create
another table total_salary_budget :
store the salary information from the salaries table.
CREATE TABLE total_salary_budget( total_budget DECIMAL(10,2) NOT NULL);
·
Use the SUM() function
that returns the total salary from the salaries table and stores this
information in the total_salary_budget table:
INSERT INTO total_salary_budget (total_budget) SELECT SUM(amount) FROM
salaries;
·
Display total_salary_budget table records :
·
Create an
AFTER DELETE trigger as after_delete_salaries that
updates the total salary into the total_salary_budget table after a row is
deleted from the salaries table.
DELIMITER $$
CREATE TRIGGER after_delete_salaries
AFTER DELETE
ON salaries FOR EACH ROW
BEGIN
UPDATE total_salary_budget SET total_budget = total_budget - old.amount;
END$$
DELIMITER ;
·
Delete a
salary from the salaries table as :
DELETE FROM salaries WHERE emp_num = 105;
·
Show the data
from the total_salary_budget table.
SELECT * FROM total_salary_budget;
Output:- the
deleted salary reduces the total_budget.
·
Remove all
data from the salaries table:
DELETE FROM salaries;
·
Show the records from total_salary_budget table.
Select * from total_salary_budget.
Output:- trigger
updated the table to zero after the execution of the query. See the below
output:
=======================================
MySQL SHOW TRIGGERS
shows all
triggers.
syntax:
SHOW TRIGGERS;
========================================================
0 Comments