Wednesday, 20 October 2021

What is trigger and before events sql videos in telugu 108

 https://youtu.be/TeSIE4gj6Fc

----------------------------------------------------------------
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       | [email protected] |
|     301 | praveen    | g         | [email protected]          |
|     302 | lakshman   | k         | [email protected]         |
|     303 | Naveen     | d         | [email protected]            |
|     304 | ambani     | e         | [email protected]             |
|     305 | saritha    | g         | [email protected]            |
|     500 | harika     | p         | [email protected]             |
+---------+------------+-----------+------------------------------+
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','[email protected]');
Query OK, 1 row affected (0.46 sec)

mysql> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email                        |
+---------+------------+-----------+------------------------------+
|     300 | venkat     | vlr       | [email protected] |
|     301 | praveen    | g         | [email protected]          |
|     302 | lakshman   | k         | [email protected]         |
|     303 | Naveen     | d         | [email protected]            |
|     304 | ambani     | e         | [email protected]             |
|     305 | saritha    | g         | [email protected]            |
|     306 | PRASAD     | a         | [email protected]             |
|     500 | harika     | p         | [email protected]             |
+---------+------------+-----------+------------------------------+
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       | [email protected] |
|     301 | praveen    | g         | [email protected]          |
|     302 | lakshman   | k         | [email protected]         |
|     303 | Naveen     | d         | [email protected]            |
|     304 | ambani     | e         | [email protected]             |
|     305 | saritha    | g         | [email protected]            |
|     306 | PRASAD     | a         | [email protected]             |
|     500 | THARUN     | p         | [email protected]             |
+---------+------------+-----------+------------------------------+
8 rows in set (0.00 sec)

mysql>

No comments:

Post a Comment

Note: only a member of this blog may post a comment.