Translate

Wednesday 20 October 2021

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>

No comments:

Post a Comment

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