-
Notifications
You must be signed in to change notification settings - Fork 1
/
sp_set_null.sql
99 lines (84 loc) · 2.87 KB
/
sp_set_null.sql
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
DROP PROCEDURE IF EXISTS dba.sp_set_null;
DELIMITER //
CREATE PROCEDURE dba.sp_set_null(
IN database_name VARCHAR(64),
IN table_name VARCHAR(64),
IN column_name VARCHAR(64),
IN primary_key INT
)
COMMENT 'Update row and save old data to recover.'
audit:BEGIN
SET @user_name = (SELECT USER());
SET @tmp_sql = CONCAT('SELECT COUNT(id) INTO @exist_row FROM ',
database_name,
'.',
table_name,
' WHERE id = ',
primary_key
);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
IF (@exist_row = 0) THEN
LEAVE audit;
END IF;
START TRANSACTION;
SET @tmp_sql = CONCAT('SELECT ',
COALESCE(column_name, 'NULL'),
' INTO @old_value ',
'FROM ',
database_name,
'.',
table_name,
' WHERE id = ',
primary_key);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET @tmp_sql = CONCAT('INSERT INTO dba.audits ('
'action,'
'database_name,'
'table_name,'
'column_name,'
'primary_key,'
'old_value,'
'new_value,'
'user_name,'
'created_at) '
'VALUES ('
'\'U\',\'',
database_name,
'\',\'',
table_name,
'\',\'',
column_name,
'\',\'',
primary_key,
'\',',
COALESCE(CONCAT('\'', @old_value, '\''), 'NULL'),
',',
COALESCE(CONCAT('\'NULL\''), 'NULL'),
',\'',
@user_name,
'\',\'',
CURRENT_TIMESTAMP,
'\')'
);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET @tmp_sql = CONCAT('UPDATE ',
database_name,
'.',
table_name,
' SET ',
column_name,
' = NULL WHERE id = ',
primary_key
);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
COMMIT;
END//
DELIMITER ;