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>