/*
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.