Wednesday, 20 October 2021

Recurring events sql videos in telugu113

 https://youtu.be/UHMXQ7ys9Ro

-------------------------------------------------------------
Recurring event
This type of event occurs after every certain interval of time. For creating this type of event we can use below command after “ON SCHEDULE”.


CREATE TABLE ramrec(recid INT AUTO_INCREMENT,
 msg VARCHAR(50),
 rtime DATETIME,
 PRIMARY KEY(recid));

-------
DROP EVENT IF EXISTS ram_test_rec;
DELIMITER $$
CREATE EVENT IF NOT EXISTS ram_test_rec
ON SCHEDULE EVERY 2 SECOND
STARTS NOW() + INTERVAL 2 SECOND
ENDS NOW() + INTERVAL 1 MINUTE
DO
BEGIN
INSERT INTO ramrec(msg, rtime)
VALUES('testing recurring event', now());
END$$
DELIMITER ;



https://phoenixnap.com/kb/mysql-event
----------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
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> CREATE TABLE ramrec(recid INT AUTO_INCREMENT,
    ->  msg VARCHAR(50),
    ->  rtime DATETIME,
    ->  PRIMARY KEY(recid));
Query OK, 0 rows affected (2.65 sec)

mysql> select * from ramrec;
Empty set (0.03 sec)

mysql> DROP EVENT IF EXISTS ram_test_rec;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> DELIMITER $$
mysql> CREATE EVENT IF NOT EXISTS ram_test_rec
    -> ON SCHEDULE EVERY 2 SECOND
    -> STARTS NOW() + INTERVAL 2 SECOND
    -> ENDS NOW() + INTERVAL 1 MINUTE
    -> DO
    -> BEGIN
    -> INSERT INTO ramrec(msg, rtime)
    -> VALUES('testing recurring event', now());
    -> END$$
Query OK, 0 rows affected (0.15 sec)

mysql> DELIMITER ;
mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg                     | rtime               |
+-------+-------------------------+---------------------+
|     1 | testing recurring event | 2021-10-10 20:16:42 |
|     2 | testing recurring event | 2021-10-10 20:16:44 |
|     3 | testing recurring event | 2021-10-10 20:16:46 |
|     4 | testing recurring event | 2021-10-10 20:16:48 |
|     5 | testing recurring event | 2021-10-10 20:16:50 |
|     6 | testing recurring event | 2021-10-10 20:16:52 |
|     7 | testing recurring event | 2021-10-10 20:16:54 |
+-------+-------------------------+---------------------+
7 rows in set (0.00 sec)

mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg                     | rtime               |
+-------+-------------------------+---------------------+
|     1 | testing recurring event | 2021-10-10 20:16:42 |
|     2 | testing recurring event | 2021-10-10 20:16:44 |
|     3 | testing recurring event | 2021-10-10 20:16:46 |
|     4 | testing recurring event | 2021-10-10 20:16:48 |
|     5 | testing recurring event | 2021-10-10 20:16:50 |
|     6 | testing recurring event | 2021-10-10 20:16:52 |
|     7 | testing recurring event | 2021-10-10 20:16:54 |
|     8 | testing recurring event | 2021-10-10 20:16:56 |
|     9 | testing recurring event | 2021-10-10 20:16:58 |
|    10 | testing recurring event | 2021-10-10 20:17:00 |
|    11 | testing recurring event | 2021-10-10 20:17:02 |
|    12 | testing recurring event | 2021-10-10 20:17:05 |
|    13 | testing recurring event | 2021-10-10 20:17:06 |
+-------+-------------------------+---------------------+
13 rows in set (0.00 sec)

mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg                     | rtime               |
+-------+-------------------------+---------------------+
|     1 | testing recurring event | 2021-10-10 20:16:42 |
|     2 | testing recurring event | 2021-10-10 20:16:44 |
|     3 | testing recurring event | 2021-10-10 20:16:46 |
|     4 | testing recurring event | 2021-10-10 20:16:48 |
|     5 | testing recurring event | 2021-10-10 20:16:50 |
|     6 | testing recurring event | 2021-10-10 20:16:52 |
|     7 | testing recurring event | 2021-10-10 20:16:54 |
|     8 | testing recurring event | 2021-10-10 20:16:56 |
|     9 | testing recurring event | 2021-10-10 20:16:58 |
|    10 | testing recurring event | 2021-10-10 20:17:00 |
|    11 | testing recurring event | 2021-10-10 20:17:02 |
|    12 | testing recurring event | 2021-10-10 20:17:05 |
|    13 | testing recurring event | 2021-10-10 20:17:06 |
|    14 | testing recurring event | 2021-10-10 20:17:08 |
|    15 | testing recurring event | 2021-10-10 20:17:10 |
|    16 | testing recurring event | 2021-10-10 20:17:12 |
|    17 | testing recurring event | 2021-10-10 20:17:14 |
|    18 | testing recurring event | 2021-10-10 20:17:16 |
+-------+-------------------------+---------------------+
18 rows in set (0.00 sec)

mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg                     | rtime               |
+-------+-------------------------+---------------------+
|     1 | testing recurring event | 2021-10-10 20:16:42 |
|     2 | testing recurring event | 2021-10-10 20:16:44 |
|     3 | testing recurring event | 2021-10-10 20:16:46 |
|     4 | testing recurring event | 2021-10-10 20:16:48 |
|     5 | testing recurring event | 2021-10-10 20:16:50 |
|     6 | testing recurring event | 2021-10-10 20:16:52 |
|     7 | testing recurring event | 2021-10-10 20:16:54 |
|     8 | testing recurring event | 2021-10-10 20:16:56 |
|     9 | testing recurring event | 2021-10-10 20:16:58 |
|    10 | testing recurring event | 2021-10-10 20:17:00 |
|    11 | testing recurring event | 2021-10-10 20:17:02 |
|    12 | testing recurring event | 2021-10-10 20:17:05 |
|    13 | testing recurring event | 2021-10-10 20:17:06 |
|    14 | testing recurring event | 2021-10-10 20:17:08 |
|    15 | testing recurring event | 2021-10-10 20:17:10 |
|    16 | testing recurring event | 2021-10-10 20:17:12 |
|    17 | testing recurring event | 2021-10-10 20:17:14 |
|    18 | testing recurring event | 2021-10-10 20:17:16 |
|    19 | testing recurring event | 2021-10-10 20:17:18 |
|    20 | testing recurring event | 2021-10-10 20:17:20 |
|    21 | testing recurring event | 2021-10-10 20:17:22 |
|    22 | testing recurring event | 2021-10-10 20:17:24 |
|    23 | testing recurring event | 2021-10-10 20:17:26 |
+-------+-------------------------+---------------------+
23 rows in set (0.00 sec)

mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg                     | rtime               |
+-------+-------------------------+---------------------+
|     1 | testing recurring event | 2021-10-10 20:16:42 |
|     2 | testing recurring event | 2021-10-10 20:16:44 |
|     3 | testing recurring event | 2021-10-10 20:16:46 |
|     4 | testing recurring event | 2021-10-10 20:16:48 |
|     5 | testing recurring event | 2021-10-10 20:16:50 |
|     6 | testing recurring event | 2021-10-10 20:16:52 |
|     7 | testing recurring event | 2021-10-10 20:16:54 |
|     8 | testing recurring event | 2021-10-10 20:16:56 |
|     9 | testing recurring event | 2021-10-10 20:16:58 |
|    10 | testing recurring event | 2021-10-10 20:17:00 |
|    11 | testing recurring event | 2021-10-10 20:17:02 |
|    12 | testing recurring event | 2021-10-10 20:17:05 |
|    13 | testing recurring event | 2021-10-10 20:17:06 |
|    14 | testing recurring event | 2021-10-10 20:17:08 |
|    15 | testing recurring event | 2021-10-10 20:17:10 |
|    16 | testing recurring event | 2021-10-10 20:17:12 |
|    17 | testing recurring event | 2021-10-10 20:17:14 |
|    18 | testing recurring event | 2021-10-10 20:17:16 |
|    19 | testing recurring event | 2021-10-10 20:17:18 |
|    20 | testing recurring event | 2021-10-10 20:17:20 |
|    21 | testing recurring event | 2021-10-10 20:17:22 |
|    22 | testing recurring event | 2021-10-10 20:17:24 |
|    23 | testing recurring event | 2021-10-10 20:17:26 |
|    24 | testing recurring event | 2021-10-10 20:17:29 |
|    25 | testing recurring event | 2021-10-10 20:17:30 |
|    26 | testing recurring event | 2021-10-10 20:17:32 |
+-------+-------------------------+---------------------+
26 rows in set (0.00 sec)

mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg                     | rtime               |
+-------+-------------------------+---------------------+
|     1 | testing recurring event | 2021-10-10 20:16:42 |
|     2 | testing recurring event | 2021-10-10 20:16:44 |
|     3 | testing recurring event | 2021-10-10 20:16:46 |
|     4 | testing recurring event | 2021-10-10 20:16:48 |
|     5 | testing recurring event | 2021-10-10 20:16:50 |
|     6 | testing recurring event | 2021-10-10 20:16:52 |
|     7 | testing recurring event | 2021-10-10 20:16:54 |
|     8 | testing recurring event | 2021-10-10 20:16:56 |
|     9 | testing recurring event | 2021-10-10 20:16:58 |
|    10 | testing recurring event | 2021-10-10 20:17:00 |
|    11 | testing recurring event | 2021-10-10 20:17:02 |
|    12 | testing recurring event | 2021-10-10 20:17:05 |
|    13 | testing recurring event | 2021-10-10 20:17:06 |
|    14 | testing recurring event | 2021-10-10 20:17:08 |
|    15 | testing recurring event | 2021-10-10 20:17:10 |
|    16 | testing recurring event | 2021-10-10 20:17:12 |
|    17 | testing recurring event | 2021-10-10 20:17:14 |
|    18 | testing recurring event | 2021-10-10 20:17:16 |
|    19 | testing recurring event | 2021-10-10 20:17:18 |
|    20 | testing recurring event | 2021-10-10 20:17:20 |
|    21 | testing recurring event | 2021-10-10 20:17:22 |
|    22 | testing recurring event | 2021-10-10 20:17:24 |
|    23 | testing recurring event | 2021-10-10 20:17:26 |
|    24 | testing recurring event | 2021-10-10 20:17:29 |
|    25 | testing recurring event | 2021-10-10 20:17:30 |
|    26 | testing recurring event | 2021-10-10 20:17:32 |
|    27 | testing recurring event | 2021-10-10 20:17:34 |
+-------+-------------------------+---------------------+
27 rows in set (0.05 sec)

mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg                     | rtime               |
+-------+-------------------------+---------------------+
|     1 | testing recurring event | 2021-10-10 20:16:42 |
|     2 | testing recurring event | 2021-10-10 20:16:44 |
|     3 | testing recurring event | 2021-10-10 20:16:46 |
|     4 | testing recurring event | 2021-10-10 20:16:48 |
|     5 | testing recurring event | 2021-10-10 20:16:50 |
|     6 | testing recurring event | 2021-10-10 20:16:52 |
|     7 | testing recurring event | 2021-10-10 20:16:54 |
|     8 | testing recurring event | 2021-10-10 20:16:56 |
|     9 | testing recurring event | 2021-10-10 20:16:58 |
|    10 | testing recurring event | 2021-10-10 20:17:00 |
|    11 | testing recurring event | 2021-10-10 20:17:02 |
|    12 | testing recurring event | 2021-10-10 20:17:05 |
|    13 | testing recurring event | 2021-10-10 20:17:06 |
|    14 | testing recurring event | 2021-10-10 20:17:08 |
|    15 | testing recurring event | 2021-10-10 20:17:10 |
|    16 | testing recurring event | 2021-10-10 20:17:12 |
|    17 | testing recurring event | 2021-10-10 20:17:14 |
|    18 | testing recurring event | 2021-10-10 20:17:16 |
|    19 | testing recurring event | 2021-10-10 20:17:18 |
|    20 | testing recurring event | 2021-10-10 20:17:20 |
|    21 | testing recurring event | 2021-10-10 20:17:22 |
|    22 | testing recurring event | 2021-10-10 20:17:24 |
|    23 | testing recurring event | 2021-10-10 20:17:26 |
|    24 | testing recurring event | 2021-10-10 20:17:29 |
|    25 | testing recurring event | 2021-10-10 20:17:30 |
|    26 | testing recurring event | 2021-10-10 20:17:32 |
|    27 | testing recurring event | 2021-10-10 20:17:34 |
|    28 | testing recurring event | 2021-10-10 20:17:36 |
+-------+-------------------------+---------------------+
28 rows in set (0.00 sec)

mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg                     | rtime               |
+-------+-------------------------+---------------------+
|     1 | testing recurring event | 2021-10-10 20:16:42 |
|     2 | testing recurring event | 2021-10-10 20:16:44 |
|     3 | testing recurring event | 2021-10-10 20:16:46 |
|     4 | testing recurring event | 2021-10-10 20:16:48 |
|     5 | testing recurring event | 2021-10-10 20:16:50 |
|     6 | testing recurring event | 2021-10-10 20:16:52 |
|     7 | testing recurring event | 2021-10-10 20:16:54 |
|     8 | testing recurring event | 2021-10-10 20:16:56 |
|     9 | testing recurring event | 2021-10-10 20:16:58 |
|    10 | testing recurring event | 2021-10-10 20:17:00 |
|    11 | testing recurring event | 2021-10-10 20:17:02 |
|    12 | testing recurring event | 2021-10-10 20:17:05 |
|    13 | testing recurring event | 2021-10-10 20:17:06 |
|    14 | testing recurring event | 2021-10-10 20:17:08 |
|    15 | testing recurring event | 2021-10-10 20:17:10 |
|    16 | testing recurring event | 2021-10-10 20:17:12 |
|    17 | testing recurring event | 2021-10-10 20:17:14 |
|    18 | testing recurring event | 2021-10-10 20:17:16 |
|    19 | testing recurring event | 2021-10-10 20:17:18 |
|    20 | testing recurring event | 2021-10-10 20:17:20 |
|    21 | testing recurring event | 2021-10-10 20:17:22 |
|    22 | testing recurring event | 2021-10-10 20:17:24 |
|    23 | testing recurring event | 2021-10-10 20:17:26 |
|    24 | testing recurring event | 2021-10-10 20:17:29 |
|    25 | testing recurring event | 2021-10-10 20:17:30 |
|    26 | testing recurring event | 2021-10-10 20:17:32 |
|    27 | testing recurring event | 2021-10-10 20:17:34 |
|    28 | testing recurring event | 2021-10-10 20:17:36 |
|    29 | testing recurring event | 2021-10-10 20:17:38 |
|    30 | testing recurring event | 2021-10-10 20:17:40 |
+-------+-------------------------+---------------------+
30 rows in set (0.00 sec)

mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg                     | rtime               |
+-------+-------------------------+---------------------+
|     1 | testing recurring event | 2021-10-10 20:16:42 |
|     2 | testing recurring event | 2021-10-10 20:16:44 |
|     3 | testing recurring event | 2021-10-10 20:16:46 |
|     4 | testing recurring event | 2021-10-10 20:16:48 |
|     5 | testing recurring event | 2021-10-10 20:16:50 |
|     6 | testing recurring event | 2021-10-10 20:16:52 |
|     7 | testing recurring event | 2021-10-10 20:16:54 |
|     8 | testing recurring event | 2021-10-10 20:16:56 |
|     9 | testing recurring event | 2021-10-10 20:16:58 |
|    10 | testing recurring event | 2021-10-10 20:17:00 |
|    11 | testing recurring event | 2021-10-10 20:17:02 |
|    12 | testing recurring event | 2021-10-10 20:17:05 |
|    13 | testing recurring event | 2021-10-10 20:17:06 |
|    14 | testing recurring event | 2021-10-10 20:17:08 |
|    15 | testing recurring event | 2021-10-10 20:17:10 |
|    16 | testing recurring event | 2021-10-10 20:17:12 |
|    17 | testing recurring event | 2021-10-10 20:17:14 |
|    18 | testing recurring event | 2021-10-10 20:17:16 |
|    19 | testing recurring event | 2021-10-10 20:17:18 |
|    20 | testing recurring event | 2021-10-10 20:17:20 |
|    21 | testing recurring event | 2021-10-10 20:17:22 |
|    22 | testing recurring event | 2021-10-10 20:17:24 |
|    23 | testing recurring event | 2021-10-10 20:17:26 |
|    24 | testing recurring event | 2021-10-10 20:17:29 |
|    25 | testing recurring event | 2021-10-10 20:17:30 |
|    26 | testing recurring event | 2021-10-10 20:17:32 |
|    27 | testing recurring event | 2021-10-10 20:17:34 |
|    28 | testing recurring event | 2021-10-10 20:17:36 |
|    29 | testing recurring event | 2021-10-10 20:17:38 |
|    30 | testing recurring event | 2021-10-10 20:17:40 |
+-------+-------------------------+---------------------+
30 rows in set (0.00 sec)

mysql> show events;
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| Db     | Name       | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status   | Originator | character_set_client | collation_connection | Database Collation |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| ramesh | upd_orders | [email protected] | SYSTEM    | ONE TIME | 2021-10-10 18:17:13 | NULL           | NULL           | NULL   | NULL | DISABLED |          1 | cp850                | cp850_general_ci     | utf8mb4_0900_ai_ci |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
1 row in set (0.33 sec)

mysql> show events/G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> show events /G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> show events \G;
*************************** 1. row ***************************
                  Db: ramesh
                Name: upd_orders
             Definer: [email protected]
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2021-10-10 18:17:13
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: DISABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

Scheduled Events sql videos in telugu112

 https://youtu.be/ihA4tiRCjAE

--------------------------------------------------------
MySQL - Scheduled Events Basics


What is Scheduled Event? –

Events are those tasks, defined in the form of SQL statements and stored in the database, to run automatically for once or at specified interval repeatedly.

MySQL events are also recognized as Temporal Triggers. Triggers, because they are activated automatically, but instead of in response to DML, they are invoked by time. Hence they are also called as Scheduled Events.

Why create Scheduled Events –

Scheduled events can be used for variety of purposes like – Optimizing database tables, cleaning up logs, archiving data, generating complex reports, etc. to name a few, during the off-peak time of use of database.

Event Scheduler –

In many ways the MySQL Event Scheduler is similar to CRON utility in Linux or Windows' task scheduler. It is a thread in the MySQL DBMS to execute all scheduled events and it must be running for that purpose.

SHOW PROCESSLIST;



SET GLOBAL event_scheduler = ON;

Setting it to OFF with the same command as above will stop the Event Scheduler.

Creating an Event –

You have to use CREATE EVENT command to create an event. The generic syntax is –

CREATE EVENT [IF NOT EXISTS] <event_name>
ON SCHEDULE <schedule>
DO
<event_body>

The event_body may contain an executable block enclosed in combined statement BEGIN..END or there can also be call to a stored procedure, or one or more SQL statements. You can drop an event, but remember than if you have used a procedure in the event then dropping it does not drop that procedure –

DROP EVENT [IF EXISTS] <event_name>;

Visit lesson next to continue to learn how to manage the Scheduled Events.


---------
DROP EVENT IF EXISTS upd_orders;

CREATE EVENT IF NOT EXISTS upd_orders
ON SCHEDULE AT NOW() + INTERVAL 1 MINUTE
## ON COMPLETION PRESERVE
DO
UPDATE orders SET amount = amount + 500;
----------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
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> select * from emp;
+--------+--------+------------+--------+------------+
| emp_id | name   | birthdate  | gender | hire_date  |
+--------+--------+------------+--------+------------+
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |
|    103 | Mike   | 1984-10-13 | M      | 2017-10-28 |
|    105 | Marie  | 1990-02-11 | F      | 2018-10-12 |
+--------+--------+------------+--------+------------+
3 rows in set (0.06 sec)

mysql> show tables;
+------------------+
| Tables_in_ramesh |
+------------------+
| customers        |
| emp              |
| orders           |
| pap_del_log      |
| pap_log          |
| papers           |
| pay              |
| students         |
| studpap          |
+------------------+
9 rows in set (0.76 sec)

mysql> select * from orders;;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 199.99 |         300 |
| 13 | 2021-09-11 | 321.50 |         301 |
| 14 | 2021-06-12 | 789.67 |         303 |
| 15 | 2021-01-03 | 102.50 |         304 |
| 16 | 2021-04-11 | 850.25 |         305 |
| 17 | 2021-03-13 | 689.25 |         500 |
| 18 | 2021-02-11 |  31.50 |         301 |
| 19 | 2021-06-12 |  79.67 |         303 |
| 20 | 2021-08-03 | 102.50 |         304 |
| 21 | 2021-07-11 |  80.25 |         305 |
| 22 | 2021-12-13 | 669.25 |         500 |
+----+------------+--------+-------------+
11 rows in set (0.02 sec)

ERROR:
No query specified

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+--------+---------+--------+------------------------+------------------+
| Id | User            | Host            | db     | Command | Time   | State                  | Info             |
+----+-----------------+-----------------+--------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL   | Daemon  | 808446 | Waiting on empty queue | NULL             |
| 35 | root            | localhost:50284 | ramesh | Query   |      0 | init                   | SHOW PROCESSLIST |
+----+-----------------+-----------------+--------+---------+--------+------------------------+------------------+
2 rows in set (0.00 sec)

mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 199.99 |         300 |
| 13 | 2021-09-11 | 321.50 |         301 |
| 14 | 2021-06-12 | 789.67 |         303 |
| 15 | 2021-01-03 | 102.50 |         304 |
| 16 | 2021-04-11 | 850.25 |         305 |
| 17 | 2021-03-13 | 689.25 |         500 |
| 18 | 2021-02-11 |  31.50 |         301 |
| 19 | 2021-06-12 |  79.67 |         303 |
| 20 | 2021-08-03 | 102.50 |         304 |
| 21 | 2021-07-11 |  80.25 |         305 |
| 22 | 2021-12-13 | 669.25 |         500 |
+----+------------+--------+-------------+
11 rows in set (0.00 sec)

mysql> DROP EVENT IF EXISTS upd_orders;
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql>
mysql> CREATE EVENT IF NOT EXISTS upd_orders
    -> ON SCHEDULE AT NOW() + INTERVAL 2 MINUTE
    -> ## ON COMPLETION PRESERVE
    -> DO
    -> UPDATE orders SET amount = amount + 500;
Query OK, 0 rows affected (0.43 sec)

mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 199.99 |         300 |
| 13 | 2021-09-11 | 321.50 |         301 |
| 14 | 2021-06-12 | 789.67 |         303 |
| 15 | 2021-01-03 | 102.50 |         304 |
| 16 | 2021-04-11 | 850.25 |         305 |
| 17 | 2021-03-13 | 689.25 |         500 |
| 18 | 2021-02-11 |  31.50 |         301 |
| 19 | 2021-06-12 |  79.67 |         303 |
| 20 | 2021-08-03 | 102.50 |         304 |
| 21 | 2021-07-11 |  80.25 |         305 |
| 22 | 2021-12-13 | 669.25 |         500 |
+----+------------+--------+-------------+
11 rows in set (0.00 sec)

mysql> show events;
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| Db     | Name       | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| ramesh | upd_orders | [email protected] | SYSTEM    | ONE TIME | 2021-10-10 18:14:52 | NULL           | NULL           | NULL   | NULL | ENABLED |          1 | cp850                | cp850_general_ci     | utf8mb4_0900_ai_ci |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.42 sec)

mysql> show events \G;
*************************** 1. row ***************************
                  Db: ramesh
                Name: upd_orders
             Definer: [email protected]
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2021-10-10 18:14:52
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show events \G;
*************************** 1. row ***************************
                  Db: ramesh
                Name: upd_orders
             Definer: [email protected]
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2021-10-10 18:14:52
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show events \G;
Empty set (0.00 sec)

ERROR:
No query specified

mysql> select * from orders;
+----+------------+---------+-------------+
| id | order_date | amount  | customer_id |
+----+------------+---------+-------------+
| 12 | 2021-05-10 |  699.99 |         300 |
| 13 | 2021-09-11 |  821.50 |         301 |
| 14 | 2021-06-12 | 1289.67 |         303 |
| 15 | 2021-01-03 |  602.50 |         304 |
| 16 | 2021-04-11 | 1350.25 |         305 |
| 17 | 2021-03-13 | 1189.25 |         500 |
| 18 | 2021-02-11 |  531.50 |         301 |
| 19 | 2021-06-12 |  579.67 |         303 |
| 20 | 2021-08-03 |  602.50 |         304 |
| 21 | 2021-07-11 |  580.25 |         305 |
| 22 | 2021-12-13 | 1169.25 |         500 |
+----+------------+---------+-------------+
11 rows in set (0.00 sec)

mysql> show events \G;
Empty set (0.00 sec)

ERROR:
No query specified

mysql> DROP EVENT IF EXISTS upd_orders;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql>
mysql> CREATE EVENT IF NOT EXISTS upd_orders
    -> ON SCHEDULE AT NOW() + INTERVAL 1 MINUTE
    -> ON COMPLETION PRESERVE
    -> DO
    -> UPDATE orders SET amount = amount + 500;
Query OK, 0 rows affected (0.15 sec)

mysql> show events;
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| Db     | Name       | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| ramesh | upd_orders | [email protected] | SYSTEM    | ONE TIME | 2021-10-10 18:17:13 | NULL           | NULL           | NULL   | NULL | ENABLED |          1 | cp850                | cp850_general_ci     | utf8mb4_0900_ai_ci |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> show events\G;
*************************** 1. row ***************************
                  Db: ramesh
                Name: upd_orders
             Definer: [email protected]
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2021-10-10 18:17:13
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show events\G;
*************************** 1. row ***************************
                  Db: ramesh
                Name: upd_orders
             Definer: [email protected]
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2021-10-10 18:17:13
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: DISABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.05 sec)

ERROR:
No query specified

mysql> select * from orders;
+----+------------+---------+-------------+
| id | order_date | amount  | customer_id |
+----+------------+---------+-------------+
| 12 | 2021-05-10 | 1199.99 |         300 |
| 13 | 2021-09-11 | 1321.50 |         301 |
| 14 | 2021-06-12 | 1789.67 |         303 |
| 15 | 2021-01-03 | 1102.50 |         304 |
| 16 | 2021-04-11 | 1850.25 |         305 |
| 17 | 2021-03-13 | 1689.25 |         500 |
| 18 | 2021-02-11 | 1031.50 |         301 |
| 19 | 2021-06-12 | 1079.67 |         303 |
| 20 | 2021-08-03 | 1102.50 |         304 |
| 21 | 2021-07-11 | 1080.25 |         305 |
| 22 | 2021-12-13 | 1669.25 |         500 |
+----+------------+---------+-------------+
11 rows in set (0.02 sec)

mysql>

Implement a trigger to log an audit record of DELETE operation sql videos in telugu111

 https://youtu.be/erTtxl3frPQ

-------------------------------------------------------
/*
Implement a trigger to log an audit record of DELETE operation 

*/
DROP TABLE IF EXISTS pap_log;
CREATE TABLE pap_log(rec_id  INT AUTO_INCREMENT,
                         use_id  VARCHAR(60),
time_at TIMESTAMP,
                         pa_student_id INT,
                         PRIMARY KEY (rec_id))
                         ENGINE = MYISAM;
 
 
DROP TRIGGER IF EXISTS del_log_papers;
DELIMITER $$

CREATE TRIGGER del_log_papers
BEFORE DELETE ON papers
FOR EACH ROW
PRECEDES stop_del
BEGIN
INSERT INTO pap_log (use_id, time_at, pa_student_id)
VALUES(USER(), NOW(), OLD.student_id);
END$$
DELIMITER ;
------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
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> DROP TABLE IF EXISTS pap_log;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> CREATE TABLE pap_log(rec_id  INT AUTO_INCREMENT,
    ->                          use_id  VARCHAR(60),
    ->  time_at TIMESTAMP,
    ->                          pa_student_id INT,
    ->                          PRIMARY KEY (rec_id))
    ->                          ENGINE = MYISAM;
Query OK, 0 rows affected (1.01 sec)

mysql>
mysql>
mysql> DROP TRIGGER IF EXISTS del_log_papers;
Query OK, 0 rows affected (0.12 sec)

mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER del_log_papers
    -> BEFORE DELETE ON papers
    -> FOR EACH ROW
    -> ##PRECEDES stop_del
    -> BEGIN
    -> INSERT INTO pap_log (use_id, time_at, pa_student_id)
    -> VALUES(USER(), NOW(), OLD.student_id);
    -> END$$
Query OK, 0 rows affected (0.32 sec)

mysql> DELIMITER ;
mysql> select * from pap_log;
Empty set (0.00 sec)

mysql> select * from papers;
+------------+-------+------------+
| title      | grade | student_id |
+------------+-------+------------+
| java       |    95 |          2 |
| python     |    99 |          2 |
| telugu     |    85 |          4 |
| mule       |    33 |          4 |
| salesforce |    93 |          1 |
| azure      |    99 |          2 |
+------------+-------+------------+
6 rows in set (0.02 sec)

mysql> delete from papers where title="java";
ERROR 1644 (45000): Not allowed to delete after 19:10 hrs
mysql> select * from pap_log;
Empty set (0.00 sec)

mysql>
mysql> DROP TRIGGER IF EXISTS del_log_papers;
Query OK, 0 rows affected (0.19 sec)

mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER del_log_papers
    -> BEFORE DELETE ON papers
    -> FOR EACH ROW
    -> PRECEDES stop_del
    -> BEGIN
    -> INSERT INTO pap_log (use_id, time_at, pa_student_id)
    -> VALUES(USER(), NOW(), OLD.student_id);
    -> END$$
Query OK, 0 rows affected (0.47 sec)

mysql> DELIMITER ;
mysql> delete from papers where title="java";
ERROR 1644 (45000): Not allowed to delete after 19:10 hrs
mysql>  select * from pap_log;
+--------+----------------+---------------------+---------------+
| rec_id | use_id         | time_at             | pa_student_id |
+--------+----------------+---------------------+---------------+
|      1 | [email protected] | 2021-10-08 21:21:06 |             2 |
+--------+----------------+---------------------+---------------+
1 row in set (0.00 sec)

mysql> delete from papers where title="java";
ERROR 1644 (45000): Not allowed to delete after 19:10 hrs
mysql>  select * from pap_log;
+--------+----------------+---------------------+---------------+
| rec_id | use_id         | time_at             | pa_student_id |
+--------+----------------+---------------------+---------------+
|      1 | [email protected] | 2021-10-08 21:21:06 |             2 |
|      2 | [email protected] | 2021-10-08 21:21:54 |             2 |
+--------+----------------+---------------------+---------------+
2 rows in set (0.00 sec)

mysql>

Trigger to stop DELETE operation sql videos in telugu110

 https://youtu.be/jyZhmiHrawA

-----------------------------------------------

Implement a trigger to stop DELETE operation on EMP table after 19:10 hrs.
or 7:10 pm.




DROP TRIGGER IF EXISTS stop_del;
DELIMITER $$

CREATE TRIGGER stop_del
BEFORE DELETE ON papers
FOR EACH ROW
BEGIN
DECLARE stop_time TIME DEFAULT TIME_FORMAT('19:10', '%H:%i');
DECLARE curr_time TIME DEFAULT TIME_FORMAT(TIME(NOW()), '%H:%i');
IF curr_time > stop_time THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Not allowed to delete after 19:10 hrs';
END IF;
END$$
DELIMITER ;

--------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
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> TIME(NOW();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIME(NOW()' at line 1
mysql> TIME(NOW());
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIME(NOW())' at line 1
mysql> NOW();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW()' at line 1
mysql> select TIME(NOW());
+-------------+
| TIME(NOW()) |
+-------------+
| 20:06:25    |
+-------------+
1 row in set (0.00 sec)

mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2021-10-08 20:06:39 |
+---------------------+
1 row in set (0.00 sec)

mysql> select TIME(NOW());
+-------------+
| TIME(NOW()) |
+-------------+
| 20:06:47    |
+-------------+
1 row in set (0.00 sec)

mysql> DROP TRIGGER IF EXISTS stop_del;
Query OK, 0 rows affected (0.33 sec)

mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER stop_del
    -> BEFORE DELETE ON papers
    -> FOR EACH ROW
    -> BEGIN
    -> DECLARE stop_time TIME DEFAULT TIME_FORMAT('19:10', '%H:%i');
    -> DECLARE curr_time TIME DEFAULT TIME_FORMAT(TIME(NOW()), '%H:%i');
    ->
    -> IF curr_time > stop_time THEN
    -> SIGNAL SQLSTATE '45000'
    -> SET MESSAGE_TEXT = 'Not allowed to delete after 17:10 hrs';
    -> END IF;
    -> END$$
Query OK, 0 rows affected (0.19 sec)

mysql> DELIMITER ;
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.00 sec)

mysql> delete from papers where grade=61;
ERROR 1644 (45000): Not allowed to delete after 17:10 hrs
mysql>
mysql> DROP TRIGGER IF EXISTS stop_del;
Query OK, 0 rows affected (0.35 sec)

mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER stop_del
    -> BEFORE DELETE ON papers
    -> FOR EACH ROW
    -> BEGIN
    -> DECLARE stop_time TIME DEFAULT TIME_FORMAT('21:10', '%H:%i');
    -> DECLARE curr_time TIME DEFAULT TIME_FORMAT(TIME(NOW()), '%H:%i');
    ->
    -> IF curr_time > stop_time THEN
    -> SIGNAL SQLSTATE '45000'
    -> SET MESSAGE_TEXT = 'Not allowed to delete after 17:10 hrs';
    -> END IF;
    -> END$$
Query OK, 0 rows affected (0.23 sec)

mysql> DELIMITER ;
mysql> delete from papers where grade=61;
Query OK, 1 row affected (0.13 sec)

mysql> DROP TRIGGER IF EXISTS stop_del;
Query OK, 0 rows affected (0.13 sec)

mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER stop_del
    -> BEFORE DELETE ON papers
    -> FOR EACH ROW
    -> BEGIN
    -> DECLARE stop_time TIME DEFAULT TIME_FORMAT('20:10', '%H:%i');
    -> DECLARE curr_time TIME DEFAULT TIME_FORMAT(TIME(NOW()), '%H:%i');
    ->
    -> IF curr_time > stop_time THEN
    -> SIGNAL SQLSTATE '45000'
    -> SET MESSAGE_TEXT = 'Not allowed to delete after 17:10 hrs';
    -> END IF;
    -> END$$
Query OK, 0 rows affected (0.13 sec)

mysql> DELIMITER ;
mysql>

What is trigger and After trigger sql videos in telugu 109

 https://youtu.be/A8reIqNsA78

--------------------------------------------------
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>

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>

Drop view sql videos in telugu 107

 https://youtu.be/moIZfW2cXsY

---------------------------------------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
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 classicmodels;
Database changed
mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int         | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| returneddate   | year        | YES  |     | NULL    |       |
| status         | varchar(29) | YES  |     | NULL    |       |
| customerNumber | int         | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

mysql> desc customers;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber         | int           | NO   | PRI | NULL    |       |
| customerName           | varchar(50)   | NO   |     | NULL    |       |
| contactLastName        | varchar(50)   | NO   |     | NULL    |       |
| contactFirstName       | varchar(50)   | NO   |     | NULL    |       |
| phone                  | varchar(50)   | NO   |     | NULL    |       |
| addressLine1           | varchar(50)   | NO   |     | NULL    |       |
| addressLine2           | varchar(50)   | YES  |     | NULL    |       |
| city                   | varchar(50)   | NO   |     | NULL    |       |
| state                  | varchar(50)   | YES  |     | NULL    |       |
| postalCode             | varchar(15)   | YES  |     | NULL    |       |
| country                | varchar(50)   | NO   |     | NULL    |       |
| salesRepEmployeeNumber | int           | YES  | MUL | NULL    |       |
| creditLimit            | decimal(10,2) | YES  |     | NULL    |       |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

mysql> create view custord as
    -> select phone ,city ,orderdate ,ordernumber from customers c,orders o where c.customerNumber=o.customerNumber;
Query OK, 0 rows affected (0.17 sec)

mysql> select * from custord limit 30;
+--------------+------------+------------+-------------+
| phone        | city       | orderdate  | ordernumber |
+--------------+------------+------------+-------------+
| 40.32.2555   | Nantes     | 2003-05-20 |       10123 |
| 40.32.2555   | Nantes     | 2004-09-27 |       10298 |
| 40.32.2555   | Nantes     | 2004-11-25 |       10345 |
| 7025551838   | Las Vegas  | 2003-05-21 |       10124 |
| 7025551838   | Las Vegas  | 2004-08-06 |       10278 |
| 7025551838   | Las Vegas  | 2004-11-29 |       10346 |
| 03 9520 4555 | Melbourne  | 2003-04-29 |       10120 |
| 03 9520 4555 | Melbourne  | 2003-05-21 |       10125 |
| 03 9520 4555 | Melbourne  | 2004-02-20 |       10223 |
| 03 9520 4555 | Melbourne  | 2004-11-24 |       10342 |
| 03 9520 4555 | Melbourne  | 2004-11-29 |       10347 |
| 40.67.8555   | Nantes     | 2004-07-23 |       10275 |
| 40.67.8555   | Nantes     | 2004-10-29 |       10315 |
| 40.67.8555   | Nantes     | 2005-02-03 |       10375 |
| 40.67.8555   | Nantes     | 2005-05-31 |       10425 |
| 07-98 9555   | Stavern    | 2003-01-29 |       10103 |
| 07-98 9555   | Stavern    | 2003-10-10 |       10158 |
| 07-98 9555   | Stavern    | 2004-10-15 |       10309 |
| 07-98 9555   | Stavern    | 2004-11-05 |       10325 |
| 4155551450   | San Rafael | 2003-03-26 |       10113 |
| 4155551450   | San Rafael | 2003-07-02 |       10135 |
| 4155551450   | San Rafael | 2003-08-08 |       10142 |
| 4155551450   | San Rafael | 2003-11-12 |       10182 |
| 4155551450   | San Rafael | 2004-03-11 |       10229 |
| 4155551450   | San Rafael | 2004-07-20 |       10271 |
| 4155551450   | San Rafael | 2004-08-20 |       10282 |
| 4155551450   | San Rafael | 2004-10-21 |       10312 |
| 4155551450   | San Rafael | 2004-11-19 |       10335 |
| 4155551450   | San Rafael | 2004-12-10 |       10357 |
| 4155551450   | San Rafael | 2005-01-19 |       10368 |
+--------------+------------+------------+-------------+
30 rows in set (0.00 sec)

mysql> create OR REPLACE view custord as
    -> select phone ,city ,orderdate ,ordernumber,status from customers c,orders o where c.customerNumber=o.customerNumber;
Query OK, 0 rows affected (0.24 sec)

mysql> select * from custord limit 30;
+--------------+------------+------------+-------------+------------+
| phone        | city       | orderdate  | ordernumber | status     |
+--------------+------------+------------+-------------+------------+
| 40.32.2555   | Nantes     | 2003-05-20 |       10123 | Shipped    |
| 40.32.2555   | Nantes     | 2004-09-27 |       10298 | Shipped    |
| 40.32.2555   | Nantes     | 2004-11-25 |       10345 | Shipped    |
| 7025551838   | Las Vegas  | 2003-05-21 |       10124 | Shipped    |
| 7025551838   | Las Vegas  | 2004-08-06 |       10278 | Shipped    |
| 7025551838   | Las Vegas  | 2004-11-29 |       10346 | Shipped    |
| 03 9520 4555 | Melbourne  | 2003-04-29 |       10120 | Shipped    |
| 03 9520 4555 | Melbourne  | 2003-05-21 |       10125 | Shipped    |
| 03 9520 4555 | Melbourne  | 2004-02-20 |       10223 | Shipped    |
| 03 9520 4555 | Melbourne  | 2004-11-24 |       10342 | Shipped    |
| 03 9520 4555 | Melbourne  | 2004-11-29 |       10347 | Shipped    |
| 40.67.8555   | Nantes     | 2004-07-23 |       10275 | Shipped    |
| 40.67.8555   | Nantes     | 2004-10-29 |       10315 | Shipped    |
| 40.67.8555   | Nantes     | 2005-02-03 |       10375 | Shipped    |
| 40.67.8555   | Nantes     | 2005-05-31 |       10425 | In Process |
| 07-98 9555   | Stavern    | 2003-01-29 |       10103 | Shipped    |
| 07-98 9555   | Stavern    | 2003-10-10 |       10158 | Shipped    |
| 07-98 9555   | Stavern    | 2004-10-15 |       10309 | Shipped    |
| 07-98 9555   | Stavern    | 2004-11-05 |       10325 | Shipped    |
| 4155551450   | San Rafael | 2003-03-26 |       10113 | Shipped    |
| 4155551450   | San Rafael | 2003-07-02 |       10135 | Shipped    |
| 4155551450   | San Rafael | 2003-08-08 |       10142 | Shipped    |
| 4155551450   | San Rafael | 2003-11-12 |       10182 | Shipped    |
| 4155551450   | San Rafael | 2004-03-11 |       10229 | Shipped    |
| 4155551450   | San Rafael | 2004-07-20 |       10271 | Shipped    |
| 4155551450   | San Rafael | 2004-08-20 |       10282 | Shipped    |
| 4155551450   | San Rafael | 2004-10-21 |       10312 | Shipped    |
| 4155551450   | San Rafael | 2004-11-19 |       10335 | Shipped    |
| 4155551450   | San Rafael | 2004-12-10 |       10357 | Shipped    |
| 4155551450   | San Rafael | 2005-01-19 |       10368 | Shipped    |
+--------------+------------+------------+-------------+------------+
30 rows in set (0.00 sec)

mysql> drop view costord;
ERROR 1051 (42S02): Unknown table 'classicmodels.costord'
mysql> drop view custord;
Query OK, 0 rows affected (0.13 sec)

mysql> show full tables;
+-------------------------+------------+
| Tables_in_classicmodels | Table_type |
+-------------------------+------------+
| accounts                | BASE TABLE |
| book                    | BASE TABLE |
| customers               | BASE TABLE |
| custview                | VIEW       |
| employees               | BASE TABLE |
| library                 | BASE TABLE |
| list                    | VIEW       |
| offices                 | BASE TABLE |
| orderdetails            | BASE TABLE |
| orders                  | BASE TABLE |
| payments                | BASE TABLE |
| productlines            | BASE TABLE |
| products                | BASE TABLE |
+-------------------------+------------+
13 rows in set (0.00 sec)

mysql>