FRE2Jn6g

1drop table if exists Students;
2
3create table Students (
4	ID int not null auto_increment primary key,
5    Name varchar(50) not null
6);
7
8drop table if exists H_Students;
9
10create table H_Students (
11	UID int not null auto_increment primary key,
12    id int not null,
13    Name varchar(50) not null,
14    BeginDate datetime,
15    EndDate datetime
16);
17
18drop trigger if exists trigger1;
19drop trigger if exists trigger2;
20drop trigger if exists trigger3;
21
22CREATE TRIGGER trigger1 AFTER INSERT ON Students
23	FOR EACH ROW
24	INSERT INTO h_students VALUES(default, new.ID, new.Name, NOW(),  NULL);
25    
26CREATE TRIGGER trigger2 AFTER DELETE ON Students
27	FOR EACH ROW
28    Update H_Students set EndDate = NOW() where old.id = H_Students.id;
29    
30    
31delimiter //
32CREATE trigger trigger3 AFTER UPDATE ON Students
33	FOR EACH ROW begin
34		Update H_Students set EndDate = NOW() where old.id = H_Students.id;
35		INSERT INTO H_Students VALUES(default, new.ID, new.Name, NOW(),  NULL);
36    end//
37delimiter ;
38
39INSERT INTO Students values(default, 'Pesho');
40INSERT INTO Students values(default, 'Misho');
41INSERT INTO Students values(default, 'Gosho');
42INSERT INTO Students values(default, 'Pochivka');
43SELECT * FROM H_Students;
44UPDATE Students set Name = 'resdfd' WHERE Name = 'Misho';
45SELECT * FROM H_Students;
46UPDATE Students set Name = 'dfghdfsssgesgsg' WHERE Name = 'resdfd';
47Select * FROM H_Students;