Translate

Wednesday, 20 October 2021

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>

No comments:

Post a Comment

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