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>