mysql DROP TABLE IF EXISTS product mysql CREATE TABLE product id INT U

 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
mysql> DROP TABLE IF EXISTS product;
mysql> CREATE TABLE product
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> title VARCHAR(30) NOT NULL DEFAULT '',
-> price FLOAT NOT NULL DEFAULT 0,
-> quo FLOAT NOT NULL DEFAULT 0,
-> PRIMARY KEY (id)
-> );
mysql> INSERT INTO product
-> VALUES
-> (1, 'Товар 1', 123, 200),
-> (2, 'Товар 2', 150, 200),
-> (3, 'Товар 3', 187, 300),
-> (4, 'Товар 4', 300, 400),
-> (5, 'Товар 5', 225, 500);
mysql> DROP TABLE IF EXISTS product_log;
mysql> CREATE TABLE product_log
-> (
-> date DATETIME COMMENT 'Дата и время операции',
-> user VARCHAR(50) COMMENT 'Кто сделал изменения',
-> product_id INT UNSIGNED COMMENT 'Код продукта',
-> old_price FLOAT COMMENT 'Старая цена',
-> new_price FLOAT COMMENT 'Новая цена'
-> );
mysql> DELIMITER |
mysql> DROP TRIGGER IF EXISTS tg_product_update |
mysql> CREATE TRIGGER tg_product_update AFTER UPDATE ON product
-> FOR EACH ROW BEGIN
-> -- Если цена изменялась...
-> IF OLD.price <> NEW.price THEN
-> -- Внесем запись в журнал
-> INSERT INTO product_log
-> VALUES (NOW(), USER(), OLD.id, OLD.price, NEW.price);
-> END IF;
-> END;
-> |
mysql> DELIMITER ;
mysql> UPDATE product SET price = price / 2 WHERE id = 2;
mysql> UPDATE product SET price = price * 2 WHERE id = 2;
mysql> SELECT * FROM product_log;
mysql> DELIMITER |
mysql> DROP TRIGGER IF EXISTS tg_product_insert |
mysql> CREATE TRIGGER tg_product_insert AFTER INSERT ON product
-> FOR EACH ROW BEGIN
-> INSERT INTO product_log
-> VALUES (NOW(), USER(), NEW.id, NULL, NEW.price);
-> END;
-> |
mysql> DROP TRIGGER IF EXISTS tg_product_delete |
mysql> CREATE TRIGGER tg_product_delete BEFORE DELETE ON product
-> FOR EACH ROW BEGIN
-> INSERT INTO product_log
-> VALUES (NOW(), USER(), OLD.id, OLD.price, NULL);
-> END;
-> |
mysql> DELIMITER ;
mysql> UPDATE product SET price = price * 1.2;
mysql> DELETE FROM product ORDER BY id DESC LIMIT 1;
mysql> INSERT INTO product VALUES (10, 'Новый товар', 123, 200);
mysql> SELECT * FROM product_log;
mysql> DELIMITER |
mysql> DROP TRIGGER IF EXISTS tg_book_delete |
mysql> CREATE TRIGGER tg_book_delete BEFORE DELETE ON books
-> FOR EACH ROW BEGIN
-> INSERT INTO product_log
-> VALUES (NOW(), USER(), OLD.id, NULL, NULL);
-> END;
-> |
mysql> DELIMITER ;
mysql> DELETE FROM bookshop.books WHERE id=20;