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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
SET GLOBAL log_bin_trust_function_creators = 1;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
status TINYINT DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE user_audit_log (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
action ENUM('INSERT', 'UPDATE', 'DELETE'),
old_values JSON,
new_values JSON,
changed_by VARCHAR(50),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DELIMITER //
CREATE TRIGGER tr_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
IF NEW.email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '邮箱格式不正确';
END IF;
IF LENGTH(NEW.username) < 3 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户名长度不能少于3个字符';
END IF;
SET NEW.created_at = NOW();
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER tr_users_after_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit_log (user_id, action, new_values, changed_by)
VALUES (
NEW.id,
'INSERT',
JSON_OBJECT(
'username', NEW.username,
'email', NEW.email,
'status', NEW.status
),
USER()
);
INSERT INTO user_profiles (user_id, nickname, created_at)
VALUES (NEW.id, NEW.username, NOW());
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER tr_users_before_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.id != NEW.id THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不允许修改用户ID';
END IF;
IF NEW.status NOT IN (0, 1, 2) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户状态值无效';
END IF;
SET NEW.updated_at = NOW();
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER tr_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit_log (user_id, action, old_values, new_values, changed_by)
VALUES (
NEW.id,
'UPDATE',
JSON_OBJECT(
'username', OLD.username,
'email', OLD.email,
'status', OLD.status
),
JSON_OBJECT(
'username', NEW.username,
'email', NEW.email,
'status', NEW.status
),
USER()
);
IF OLD.status != NEW.status THEN
INSERT INTO user_status_history (user_id, old_status, new_status, changed_at)
VALUES (NEW.id, OLD.status, NEW.status, NOW());
END IF;
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER tr_users_before_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
DECLARE order_count INT DEFAULT 0;
SELECT COUNT(*) INTO order_count
FROM orders
WHERE user_id = OLD.id;
IF order_count > 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '用户有关联订单，不能删除，请先处理相关数据';
END IF;
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER tr_users_after_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit_log (user_id, action, old_values, changed_by)
VALUES (
OLD.id,
'DELETE',
JSON_OBJECT(
'username', OLD.username,
'email', OLD.email,
'status', OLD.status
),
USER()
);
DELETE FROM user_profiles WHERE user_id = OLD.id;
DELETE FROM user_sessions WHERE user_id = OLD.id;
END //
DELIMITER ;
|
