· 7 years ago · Feb 22, 2019, 08:12 AM
1USE demo;
2
3/* DEMO FOR TRACKING HISTORY */
4
5DROP TABLE IF EXISTS users;
6DROP TABLE IF EXISTS user_history;
7DROP PROCEDURE IF EXISTS append_user_history;
8
9CREATE TABLE users (
10 user_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
11 email varchar(255) NOT NULL UNIQUE,
12 plan_id int NOT NULL DEFAULT 1,
13 status enum('active','cancel') NOT NULL DEFAULT 'active',
14 date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
15 date_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
16);
17
18CREATE TABLE user_history (
19 user_id int NOT NULL,
20 email varchar(255) NOT NULL,
21 plan_id int NOT NULL,
22 status enum('active','cancel') NOT NULL,
23 date_added timestamp NOT NULL,
24 date_updated timestamp NOT NULL,
25 CONSTRAINT pk_user_history PRIMARY KEY (user_id, date_updated)
26);
27
28DELIMITER $$
29CREATE TRIGGER tr_users_insert_append_user_history AFTER INSERT ON users
30FOR EACH ROW
31BEGIN
32 INSERT INTO user_history(user_id, email, plan_id, status, date_added, date_updated)
33 VALUES (NEW.user_id, NEW.email, NEW.plan_id, NEW.status, NEW.date_added, NEW.date_updated);
34END $$
35DELIMITER ;
36
37SELECT * FROM users;
38SELECT * FROM user_history;
39
40INSERT INTO users (email)
41VALUES ('ness@storyblocks.com');
42
43INSERT INTO users (email, plan_id)
44VALUES ('paula@storyblocks.com', 2);
45
46SELECT * FROM users;
47SELECT * FROM user_history;
48
49UPDATE users SET status = 'cancel' WHERE user_id = 1;
50
51SELECT * FROM users;
52SELECT * FROM user_history;
53
54DELIMITER $$
55CREATE TRIGGER tr_users_update_append_user_history AFTER UPDATE ON users
56FOR EACH ROW
57BEGIN
58 INSERT INTO user_history(user_id, email, plan_id, status, date_added, date_updated)
59 VALUES (NEW.user_id, NEW.email, NEW.plan_id, NEW.status, NEW.date_added, NEW.date_updated);
60END $$
61DELIMITER ;
62
63UPDATE users SET email = 'jeff@storyblocks.com', status = 'cancel' WHERE user_id = 2;
64
65SELECT * FROM users;
66SELECT * FROM user_history;
67
68UPDATE users SET status = 'active' WHERE user_id IN (1,2);
69
70SELECT * FROM users;
71SELECT * FROM user_history;
72
73DELIMITER $$
74CREATE PROCEDURE append_user_history(IN user_id int, IN email varchar(255), IN plan_id int, IN status enum('active','cancel'), IN date_added timestamp, IN date_updated timestamp)
75BEGIN
76 INSERT INTO user_history(user_id, email, plan_id, status, date_added, date_updated)
77 VALUES (user_id, email, plan_id, status, date_added, date_updated);
78END $$
79DELIMITER ;
80
81DROP TRIGGER tr_users_insert_append_user_history;
82DROP TRIGGER tr_users_update_append_user_history;
83
84DELIMITER $$
85CREATE TRIGGER tr_users_insert_append_user_history AFTER INSERT ON users
86FOR EACH ROW
87BEGIN
88 CALL append_user_history(NEW.user_id, NEW.email, NEW.plan_id, NEW.status, NEW.date_added, NEW.date_updated);
89END $$
90DELIMITER ;
91
92DELIMITER $$
93CREATE TRIGGER tr_users_update_append_user_history AFTER UPDATE ON users
94FOR EACH ROW
95BEGIN
96 CALL append_user_history(NEW.user_id, NEW.email, NEW.plan_id, NEW.status, NEW.date_added, NEW.date_updated);
97END $$
98DELIMITER ;
99
100INSERT INTO users (email, plan_id)
101VALUES ('poo@storyblocks.com', 3);
102
103UPDATE users SET email = 'pokey@storyblocks.com' WHERE email = 'poo@storyblocks.com';
104
105SELECT * FROM users;
106SELECT * FROM user_history;
107
108SHOW TRIGGERS WHERE `table` = 'users';
109
110DROP TABLE users;
111DROP TABLE user_history;
112DROP PROCEDURE append_user_history;
113
114
115/* DEMO FOR FORCING INTEGRITY */
116
117DROP TABLE IF EXISTS item_libraries;
118DROP PROCEDURE IF EXISTS check_for_library_conflicts;
119
120CREATE TABLE item_libraries (
121 item_id int NOT NULL,
122 library_id int NOT NULL,
123 CONSTRAINT pk_stock_item_stock_libraries PRIMARY KEY (item_id, library_id)
124);
125
126DELIMITER $$
127CREATE PROCEDURE check_for_library_conflicts(IN item_id int, IN library_id int)
128BEGIN
129 IF library_id IN (1, 2)
130 AND (
131 SELECT count(*)
132 FROM item_libraries l
133 WHERE l.item_id = item_id
134 AND l.library_id IN (1, 2)
135 AND l.library_id <> library_id
136 ) > 0
137 THEN
138 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Items cannot be in both libraries 1 and 2';
139 END IF;
140END $$
141DELIMITER ;
142
143DELIMITER $$
144CREATE TRIGGER tr_item_libraries_insert_check_for_library_conflicts BEFORE INSERT ON item_libraries
145FOR EACH ROW
146BEGIN
147 CALL check_for_library_conflicts(NEW.item_id, NEW.library_id);
148END $$
149DELIMITER ;
150
151DELIMITER $$
152CREATE TRIGGER tr_item_libraries_update_check_for_library_conflicts BEFORE UPDATE ON item_libraries
153FOR EACH ROW
154BEGIN
155 CALL check_for_library_conflicts(NEW.item_id, NEW.library_id);
156END $$
157DELIMITER ;
158
159INSERT INTO item_libraries (item_id, library_id)
160VALUES (101,1), (102,1), (103,1), (201,2), (202,2), (203,2), (301,3), (302,3), (303,3);
161
162SELECT * FROM item_libraries;
163
164INSERT INTO item_libraries (item_id, library_id)
165VALUES (101, 3);
166
167SELECT * FROM item_libraries;
168
169INSERT INTO item_libraries (item_id, library_id)
170VALUES (101, 2);
171
172INSERT INTO item_libraries (item_id, library_id)
173VALUES (202, 1);
174
175INSERT INTO item_libraries (item_id, library_id)
176VALUES (102, 3), (202, 1), (202, 3);
177
178SELECT * FROM item_libraries;
179
180DROP TABLE item_libraries;
181DROP PROCEDURE check_for_library_conflicts;