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 | root@localhost | 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 | root@localhost | 2021-10-08 21:21:06 |             2 |

|      2 | root@localhost | 2021-10-08 21:21:54 |             2 |

+--------+----------------+---------------------+---------------+

2 rows in set (0.00 sec)

mysql>