LcRdhbAD

1DROP TABLE IF EXISTS StudentsLog;
2
3CREATE TABLE StudentsLog(
4	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
5    StudentId INTEGER NOT NULL,
6	Name VARCHAR(150) NOT NULL,
7	Num INTEGER NOT NULL,
8	ClassNum INTEGER NOT NULL,
9	ClassLetter CHAR(1) NOT NULL,
10	Birthday DATE,
11	EGN CHAR(10),
12	PhysicsExamResult NUMERIC(3,2),
13    ActionDone Char(12),
14    DateInserts DATETIME,
15    
16    FOREIGN KEY(StudentId) REFERENCES Students(Id)
17);
18
19
20DROP TRIGGER IF exists RowHistoryInsert;
21DELIMITER $
22CREATE TRIGGER RowHistoryInsert
23AFTER INSERT
24ON Students FOR EACH ROW
25BEGIN 
26	INSERT INTO StudentsLog(StudentId, Name, Num, ClassNum, ClassLetter, Birthday, EGN, DateInserts, ActionDone) VALUES(new.Id, new.Name, new.Num, new.ClassNum, new.ClassLetter, new.Birthday, new.EGN, sysdate(), 'I');
27END$
28
29DELIMITER ;
30
31DROP TRIGGER IF EXISTS RowHistoryUpdate;
32
33DELIMITER $
34CREATE TRIGGER RowHistoryUpdate
35AFTER UPDATE
36ON Students FOR EACH ROW
37BEGIN 
38	INSERT INTO StudentsLog(StudentId, Name, Num, ClassNum, ClassLetter, Birthday, EGN, DateInserts, ActionDone) VALUES(new.Id, new.Name, new.Num, new.ClassNum, new.ClassLetter, new.Birthday, new.EGN, sysdate(), 'U');
39END$
40DELIMITER ;
41
42
43DROP TRIGGER IF exists RowHistoryDelete;
44
45DELIMITER $
46CREATE TRIGGER RowHistoryDelete
47AFTER DELETE
48ON Students FOR EACH ROW
49BEGIN 
50	INSERT INTO StudentsLog(StudentId, Name, Num, ClassNum, ClassLetter, Birthday, EGN, DateInserts, ActionDone) VALUES(old.Id ,old.Name, old.Num, old.ClassNum, old.ClassLetter, old.Birthday, old.EGN, sysdate(), 'D');
51END$
52
53DELIMITER ;
54
55
56INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
57
58UPDATE Students
59SET Name='Milko'
60WHERE Id = 105;
61
62DELETE FROM Students
63Where Id = 105;