A SQL trigger is a database object which fires when an event occurs in a database. We can execute a SQL query that will "do something" in a database when a change occurs on a database table such as a record is inserted or updated or deleted.
A trigger is called a special procedure because it cannot be called directly like a stored procedure. The main difference between the trigger and procedure is that a trigger is called automatically when a data modification event is made against a table. In contrast, a stored procedure must be called explicitly.
use ramesh;
show tables;
desc papers;
desc students;
CREATE TRIGGER trigger_name
(AFTER | BEFORE) (INSERT | UPDATE | DELETE)
ON table_name FOR EACH ROW
BEGIN
--variable declarations
--trigger code
END;
--------------------------------
/*
implement a business rule that no papers can have
more than 3 students in papers table.
*/
DROP TRIGGER IF EXISTS studcount_insert;
DELIMITER $$
CREATE TRIGGER studcount_insert
AFTER INSERT ON papers
FOR EACH ROW
BEGIN
DECLARE cnt INT DEFAULT 0;
DECLARE num INT DEFAULT 3;
SELECT COUNT(*) INTO cnt FROM papers
WHERE student_id = new.student_id;
IF cnt > num THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT ='no more papers allowed';
END IF;
END $$
DELIMITER ;
----------------
DROP TRIGGER IF EXISTS studcount_update;
DELIMITER $$
CREATE TRIGGER studcount_update
AFTER UPDATE ON papers
FOR EACH ROW
BEGIN
DECLARE cnt INT DEFAULT 0;
DECLARE num INT DEFAULT 3;
SELECT COUNT(*) INTO cnt FROM papers
WHERE student_id = new.student_id;
IF cnt > num THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT ='no more papers allowed';
END IF;
END $$
DELIMITER ;
-----------------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use ramesh
Database changed
mysql> desc papers;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| title | varchar(100) | YES | | NULL | |
| grade | int | YES | | NULL | |
| student_id | int | YES | MUL | NULL | |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.09 sec)
mysql> desc students;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| first_name | varchar(100) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from students;
+----+------------+
| id | first_name |
+----+------------+
| 1 | ramesh |
| 2 | vinod |
| 3 | lakshman |
| 4 | praveen |
| 5 | harika |
+----+------------+
5 rows in set (0.01 sec)
mysql> select * from papers;
+--------+-------+------------+
| title | grade | student_id |
+--------+-------+------------+
| ds | 61 | 1 |
| c | 72 | 1 |
| java | 95 | 2 |
| python | 99 | 2 |
| telugu | 85 | 4 |
+--------+-------+------------+
5 rows in set (0.00 sec)
mysql> insert into papers values(1,33,"mule");
ERROR 1366 (HY000): Incorrect integer value: 'mule' for column 'student_id' at row 1
mysql> insert into papers values("mule",33,1);
Query OK, 1 row affected (0.20 sec)
mysql> insert into papers values("salesforce",93,1);
Query OK, 1 row affected (0.10 sec)
mysql> select * from papers;
+------------+-------+------------+
| title | grade | student_id |
+------------+-------+------------+
| ds | 61 | 1 |
| c | 72 | 1 |
| java | 95 | 2 |
| python | 99 | 2 |
| telugu | 85 | 4 |
| mule | 33 | 1 |
| salesforce | 93 | 1 |
+------------+-------+------------+
7 rows in set (0.00 sec)
mysql> DROP TRIGGER IF EXISTS studcount_insert;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> DELIMITER $$
mysql> CREATE TRIGGER studcount_insert
-> AFTER INSERT ON papers
-> FOR EACH ROW
-> BEGIN
-> DECLARE cnt INT DEFAULT 0;
-> DECLARE num INT DEFAULT 3;
->
-> SELECT COUNT(*) INTO cnt FROM papers
-> WHERE student_id = new.student_id;
->
-> IF cnt > num THEN
-> SIGNAL SQLSTATE '45000'
-> SET MESSAGE_TEXT ='no more papers allowed';
-> END IF;
-> END $$
Query OK, 0 rows affected (0.12 sec)
mysql> DELIMITER ;
mysql> insert into papers values("azure",99,1);
ERROR 1644 (45000): no more papers allowed
mysql> insert into papers values("azure",99,2);
Query OK, 1 row affected (0.11 sec)
mysql> insert into papers values("aws",99,2);
ERROR 1644 (45000): no more papers allowed
mysql> select * From papers;
+------------+-------+------------+
| title | grade | student_id |
+------------+-------+------------+
| ds | 61 | 1 |
| c | 72 | 1 |
| java | 95 | 2 |
| python | 99 | 2 |
| telugu | 85 | 4 |
| mule | 33 | 1 |
| salesforce | 93 | 1 |
| azure | 99 | 2 |
+------------+-------+------------+
8 rows in set (0.00 sec)
mysql> DROP TRIGGER IF EXISTS studcount_update;
Query OK, 0 rows affected (0.14 sec)
mysql> DELIMITER $$
mysql> CREATE TRIGGER studcount_update
-> AFTER UPDATE ON papers
-> FOR EACH ROW
-> BEGIN
-> DECLARE cnt INT DEFAULT 0;
-> DECLARE num INT DEFAULT 3;
->
-> SELECT COUNT(*) INTO cnt FROM papers
-> WHERE student_id = new.student_id;
->
-> IF cnt > num THEN
-> SIGNAL SQLSTATE '45000'
-> SET MESSAGE_TEXT ='no more employees allowed';
-> END IF;
-> END $$
Query OK, 0 rows affected (0.14 sec)
mysql> DELIMITER ;
mysql> update papers set student_id=2 where student_id=4;
ERROR 1644 (45000): no more employees allowed
mysql> update papers set student_id=4 where title="mule";
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from papers;
+------------+-------+------------+
| title | grade | student_id |
+------------+-------+------------+
| ds | 61 | 1 |
| c | 72 | 1 |
| java | 95 | 2 |
| python | 99 | 2 |
| telugu | 85 | 4 |
| mule | 33 | 4 |
| salesforce | 93 | 1 |
| azure | 99 | 2 |
+------------+-------+------------+
8 rows in set (0.05 sec)
mysql>