-
Notifications
You must be signed in to change notification settings - Fork 1
/
Asg_7.txt
143 lines (124 loc) · 5.09 KB
/
Asg_7.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
AARTI RATHI
My website - https://shinchancode.github.io/3d-react-portfolio/
Problem Statement :
Write a database trigger (Row level and statement level)
-----------------------------------------------------------------------------------------------------------------
mysql> use mydb;
Database changed
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| department |
| employee |
| v1 |
| v2 |
| v3 |
| v4 |
+----------------+
6 rows in set (0.00 sec)
mysql> select * from employee;
+-------+--------------+-----------+------------+------------+--------+------+------+
| empid | ename | contactno | DOB | DOJ | salary | Pid | did |
+-------+--------------+-----------+------------+------------+--------+------+------+
| 1256 | Aarti Rathi | 77852 | 2000-10-17 | 2017-10-14 | 18025 | 2 | 5 |
| 1257 | Aditya Rathi | 96890 | 0000-00-00 | 2014-03-27 | 22005 | 4 | 1 |
| 1271 | Binod | 78523 | 1995-08-10 | 2018-07-11 | 26418 | 3 | 4 |
| 1222 | Anushka | 77453 | 1998-04-01 | 2019-12-12 | 28521 | 1 | 5 |
+-------+--------------+-----------+------------+------------+--------+------+------+
4 rows in set (0.01 sec)
mysql> select * from department;
+-----+--------+
| DID | dname |
+-----+--------+
| 1 | ENTC |
| 3 | ENTC |
| 4 | IT |
| 6 | COMP-A |
| 8 | IT |
| 9 | COMP-B |
+-----+--------+
6 rows in set (0.00 sec)
mysql> create table empaudit(id int, sal int);
Query OK, 0 rows affected (0.86 sec)
mysql> delimiter $$
mysql> create trigger empt1
-> before update on employee
-> for each row
-> begin
-> insert into empaudit values(old.empid, old.salary);
-> end$$
Query OK, 0 rows affected (0.30 sec)
mysql> select * from empaudit$$
Empty set (0.00 sec)
mysql> update employee set salary=salary+2000$$
Query OK, 4 rows affected (0.15 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from employee$$
+-------+--------------+-----------+------------+------------+--------+------+------+
| empid | ename | contactno | DOB | DOJ | salary | Pid | did |
+-------+--------------+-----------+------------+------------+--------+------+------+
| 1256 | Aarti Rathi | 77852 | 2000-10-17 | 2017-10-14 | 20025 | 2 | 5 |
| 1257 | Aditya Rathi | 96890 | 0000-00-00 | 2014-03-27 | 24005 | 4 | 1 |
| 1271 | Binod | 78523 | 1995-08-10 | 2018-07-11 | 28418 | 3 | 4 |
| 1222 | Anushka | 77453 | 1998-04-01 | 2019-12-12 | 30521 | 1 | 5 |
+-------+--------------+-----------+------------+------------+--------+------+------+
4 rows in set (0.01 sec)
mysql> select * from empaudit$$
+------+-------+
| id | sal |
+------+-------+
| 1256 | 18025 |
| 1257 | 22005 |
| 1271 | 26418 |
| 1222 | 28521 |
+------+-------+
4 rows in set (0.03 sec)
mysql> create table empb(E_id int, pay int);
-> $$
Query OK, 0 rows affected (2.09 sec)
mysql> create trigger empt2
-> before delete on employee
-> for each row
-> begin
-> insert into empb values(old.empid, old.salary);
-> end$$
Query OK, 0 rows affected (0.51 sec)
mysql> delete from employee where empid=1222$$
Query OK, 1 row affected (0.33 sec)
mysql> select * from empb$$
+------+-------+
| E_id | pay |
+------+-------+
| 1222 | 30521 |
+------+-------+
1 row in set (0.00 sec)
mysql> select * from employee$$
+-------+--------------+-----------+------------+------------+--------+------+------+
| empid | ename | contactno | DOB | DOJ | salary | Pid | did |
+-------+--------------+-----------+------------+------------+--------+------+------+
| 1256 | Aarti Rathi | 77852 | 2000-10-17 | 2017-10-14 | 20025 | 2 | 5 |
| 1257 | Aditya Rathi | 96890 | 0000-00-00 | 2014-03-27 | 24005 | 4 | 1 |
| 1271 | Binod | 78523 | 1995-08-10 | 2018-07-11 | 28418 | 3 | 4 |
+-------+--------------+-----------+------------+------------+--------+------+------+
3 rows in set (0.00 sec)
mysql> create trigger empt3
-> before update on employee
-> for each row
-> begin
-> if new.salary>20000 then
-> set new.salary=20000;
-> end if;
-> end$$
Query OK, 0 rows affected (0.28 sec)
mysql> select * from employee$$
+-------+--------------+-----------+------------+------------+--------+------+------+
| empid | ename | contactno | DOB | DOJ | salary | Pid | did |
+-------+--------------+-----------+------------+------------+--------+------+------+
| 1256 | Aarti Rathi | 77852 | 2000-10-17 | 2017-10-14 | 20025 | 2 | 5 |
| 1257 | Aditya Rathi | 96890 | 0000-00-00 | 2014-03-27 | 24005 | 4 | 1 |
| 1271 | Binod | 78523 | 1995-08-10 | 2018-07-11 | 28418 | 3 | 4 |
+-------+--------------+-----------+------------+------------+--------+------+------+
3 rows in set (0.24 sec)
mysql> drop trigger empt3$$
Query OK, 0 rows affected (0.44 sec)