SQL/SQL10-Basic_Trigger at main · DaviDiasBR/SQL · GitHub

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

create database Sistema;

use Sistema;

-- Create Tables --

create table usuarioSistema(
idUsuario int primary key auto_increment,
nameUsuario varchar (30),
loginUsuario varchar (30),
senha varchar (30)
);

create table bkp_usuarioSistema(
idBackup int primary key auto_increment,
idUsuario int,
nameUsuario varchar (30),
loginUsuario varchar (30)
);

show tables;

+--------------------+
| Tables_in_sistema  |
+--------------------+
| bkp_usuariosistema |
| usuariosistema     |
+--------------------+

desc usuarioSistema;

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| idUsuario    | int(11)     | NO   | PRI | NULL    | auto_increment |
| nameUsuario  | varchar(30) | YES  |     | NULL    |                |
| loginUsuario | varchar(30) | YES  |     | NULL    |                |
| senha        | varchar(30) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

desc bkp_usuarioSistema

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| idBackup     | int(11)     | NO   | PRI | NULL    | auto_increment |
| idUsuario    | int(11)     | YES  |     | NULL    |                |
| nameUsuario  | varchar(30) | YES  |     | NULL    |                |
| loginUsuario | varchar(30) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

-- Create Trigger --

demiliter //

create trigger bkp_usuarioSistema
before delete on usuarioSistema
for each row
	begin
insert into bkp_usuarioSistema values
	(null, old.idUsuario, old.nameUsuario, old.loginUsuario);
	end //

-- Insert Data --

insert into usuarioSistema (nameUsuario, loginUsuario, senha) values
	('Davi', 'DaviDias', '1234'),
	('Ryan', 'RyanDias', '5678'),
	('Noah','NoahDias','9101')
	//

select * from usuarioSistema//

+-----------+-------------+--------------+-------+
| idUsuario | nameUsuario | loginUsuario | senha |
+-----------+-------------+--------------+-------+
|         1 | Davi        | DaviDias     | 1234  |
|         2 | Ryan        | RyanDias     | 5678  |
|         3 | Noah        | NoahDias     | 9101  |
+-----------+-------------+--------------+-------+

-- Delete Data --

delete from usuarioSistema
	where idUsuario = 1 //

select * from usuarioSistema//

+-----------+-------------+--------------+-------+
| idUsuario | nameUsuario | loginUsuario | senha |
+-----------+-------------+--------------+-------+
|         2 | Ryan        | RyanDias     | 5678  |
|         3 | Noah        | NoahDias     | 9101  |
+-----------+-------------+--------------+-------+

select * from bkp_usuarioSistema//

+----------+-----------+-------------+--------------+
| idBackup | idUsuario | nameUsuario | loginUsuario |
+----------+-----------+-------------+--------------+
|        1 |         1 | Davi        | DaviDias     |
+----------+-----------+-------------+--------------+