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;
--------------------------------
DROP TRIGGER IF EXISTS upper_case_insert;
DELIMITER $$
CREATE TRIGGER upper_case_insert
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
SET NEW.first_name = UPPER(NEW.first_name);
SET NEW.email = UPPER(new.email);
END$$
DELIMITER ;
DROP TRIGGER IF EXISTS upper_case_update;
DELIMITER $$
CREATE TRIGGER upper_case_update
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
SET NEW.first_name = UPPER(NEW.first_name);
SET NEW.email = UPPER(new.email);
END$$
DELIMITER ;
---------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
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>
mysql> show tables;
+------------------+
| Tables_in_ramesh |
+------------------+
| customers |
| emp |
| orders |
| papers |
| pay |
| students |
+------------------+
6 rows in set (0.03 sec)
mysql>
mysql>
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.01 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> create view studpap as
-> SELECT id,title
-> FROM students
-> LEFT JOIN papers
-> ON students.id = papers.student_id;
Query OK, 0 rows affected (0.22 sec)
mysql> select * from studpap;
+----+--------+
| id | title |
+----+--------+
| 1 | c |
| 1 | ds |
| 2 | python |
| 2 | java |
| 3 | NULL |
| 4 | telugu |
| 5 | NULL |
+----+--------+
7 rows in set (0.12 sec)
mysql> INSERT INTO studpap VALUES (55,"Mulesoft");
ERROR 1471 (HY000): The target table studpap of the INSERT is not insertable-into
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> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email |
+---------+------------+-----------+------------------------------+
| 300 | venkat | vlr | venkat.vlrtraining@gmail.com |
| 301 | praveen | g | g.praveen@gmail.com |
| 302 | lakshman | k | lakshman.k@gmail.com |
| 303 | Naveen | d | Naveend@gmail.com |
| 304 | ambani | e | e.ambani@aol.com |
| 305 | saritha | g | saritha@gmail.com |
| 500 | harika | p | harika.p@aol.com |
+---------+------------+-----------+------------------------------+
7 rows in set (0.04 sec)
mysql> DROP TRIGGER IF EXISTS upper_case_insert;
Query OK, 0 rows affected, 1 warning (0.13 sec)
mysql> DELIMITER $$
mysql> CREATE TRIGGER upper_case_insert
-> BEFORE INSERT ON customers
-> FOR EACH ROW
-> BEGIN
-> SET NEW.name = UPPER(NEW.first_name);
-> SET NEW.loc = UPPER(new.email);
-> END$$
ERROR 1054 (42S22): Unknown column 'name' in 'NEW'
mysql> DELIMITER ;
mysql> DROP TRIGGER IF EXISTS upper_case_insert;
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql>
mysql> DELIMITER $$
mysql> CREATE TRIGGER upper_case_insert
-> BEFORE INSERT ON customers
-> FOR EACH ROW
-> BEGIN
-> SET NEW.first_name = UPPER(NEW.first_name);
-> SET NEW.email = UPPER(new.email);
-> END$$
Query OK, 0 rows affected (0.68 sec)
mysql> DELIMITER ;
mysql> insert into customers values(306,'prasad','a','prasad@gmail.com');
Query OK, 1 row affected (0.46 sec)
mysql> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email |
+---------+------------+-----------+------------------------------+
| 300 | venkat | vlr | venkat.vlrtraining@gmail.com |
| 301 | praveen | g | g.praveen@gmail.com |
| 302 | lakshman | k | lakshman.k@gmail.com |
| 303 | Naveen | d | Naveend@gmail.com |
| 304 | ambani | e | e.ambani@aol.com |
| 305 | saritha | g | saritha@gmail.com |
| 306 | PRASAD | a | PRASAD@GMAIL.COM |
| 500 | harika | p | harika.p@aol.com |
+---------+------------+-----------+------------------------------+
8 rows in set (0.00 sec)
mysql> DROP TRIGGER IF EXISTS upper_case_update;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> DELIMITER $$
mysql> CREATE TRIGGER upper_case_update
-> BEFORE UPDATE ON customers
-> FOR EACH ROW
-> BEGIN
-> SET NEW.first_name = UPPER(NEW.first_name);
-> SET NEW.email = UPPER(new.email);
-> END$$
Query OK, 0 rows affected (0.17 sec)
mysql> DELIMITER ;
mysql> update customers set first_name="Tharun" where cust_id=500;
Query OK, 1 row affected (0.31 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email |
+---------+------------+-----------+------------------------------+
| 300 | venkat | vlr | venkat.vlrtraining@gmail.com |
| 301 | praveen | g | g.praveen@gmail.com |
| 302 | lakshman | k | lakshman.k@gmail.com |
| 303 | Naveen | d | Naveend@gmail.com |
| 304 | ambani | e | e.ambani@aol.com |
| 305 | saritha | g | saritha@gmail.com |
| 306 | PRASAD | a | PRASAD@GMAIL.COM |
| 500 | THARUN | p | HARIKA.P@AOL.COM |
+---------+------------+-----------+------------------------------+
8 rows in set (0.00 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.