SQL-design/triggerset.php at master · mimaoxiao/SQL-design · GitHub

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
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
create table logsp
(
ID int not null auto_increment,
timing datetime not null,
tablename varchar(20) not null,
itemaim int not null,
action varchar(10) not null,
actionaim varchar(20),
bef varchar(255),
aft varchar(255),
primary key(ID)
)

delimiter $
create trigger I_com
after insert on computer for each row
begin
    insert into logsp (timing,tablename,itemaim,action)
    values(now(),'computer',new.ID,'insert');
end$

create trigger U_com
after update on computer for each row
begin
    if(new.type!=old.type)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'computer',new.ID,'update','type',old.type,new.type);
        end if;
    if(new.remark!=old.remark)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'computer',new.ID,'update','remark',old.remark,new.remark);
        end if;
    if(new.photo_url!=old.photo_url)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'computer',new.ID,'update','photo_url',old.photo_url,new.photo_url);
        end if;
    if(new.owner_ID!=old.owner_ID)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'computer',new.ID,'update','owner_ID',cast(old.owner_ID as char),cast(new.owner_ID as char));
        end if;
    if(new.status!=old.status)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'computer',new.ID,'update','status',cast(old.status as char),cast(new.status as char));
        end if;
    if(new.avaliable!=old.avaliable)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'computer',new.ID,'update','avaliable',cast(old.avaliable as char),cast(new.avaliable as char));
        end if;
end$

create trigger I_item
after insert on item for each row
begin
    insert into logsp (timing,tablename,itemaim,action)
    values(now(),'item',new.ID,'insert');
end$

create trigger U_item
after update on item for each row
begin
    if(new.message!=old.message)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'item',new.ID,'update','message',old.message,new.message);
        end if;
    if(new.photo_url!=old.photo_url)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'item',new.ID,'update','photo_url',old.photo_url,new.photo_url);
        end if;
    if(new.owner_ID!=old.owner_ID)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'item',new.ID,'update','owner_ID',cast(old.owner_ID as char),cast(new.owner_ID as char));
        end if;
    if(new.status!=old.status)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'item',new.ID,'update','status',cast(old.status as char),cast(new.status as char));
        end if;
    if(new.avaliable!=old.avaliable)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'item',new.ID,'update','avaliable',cast(old.avaliable as char),cast(new.avaliable as char));
        end if;
end$

create trigger I_donor
after insert on donor for each row
begin
    insert into logsp (timing,tablename,itemaim,action)
    values(now(),'donor',new.ID,'insert');
end$

create trigger U_donor
after update on donor for each row
begin
    if(new.name!=old.name)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donor',new.ID,'update','name',old.name,new.name);
        end if;
    if(new.type!=old.type)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donor',new.ID,'update','type',old.type,new.type);
        end if;
    if(new.email!=old.email)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donor',new.ID,'update','email',old.email,new.email);
        end if;
    if(new.phone!=old.phone)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donor',new.ID,'update','phone',old.phone,new.phone);
        end if;
    if(new.remark!=old.remark)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donor',new.ID,'update','remark',old.remark,new.remark);
        end if;
    if(new.avaliable!=old.avaliable)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donor',new.ID,'update','avaliable',cast(old.avaliable as char),cast(new.avaliable as char));
        end if;
end$

create trigger I_donee
after insert on donee for each row
begin
    insert into logsp (timing,tablename,itemaim,action)
    values(now(),'donee',new.ID,'insert');
end$

create trigger U_donee
after update on donee for each row
begin
    if(new.name!=old.name)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donee',new.ID,'update','name',old.name,new.name);
        end if;
    if(new.type!=old.type)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donee',new.ID,'update','type',old.type,new.type);
        end if;
    if(new.email!=old.email)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donee',new.ID,'update','email',old.email,new.email);
        end if;
    if(new.phone!=old.phone)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donee',new.ID,'update','phone',old.phone,new.phone);
        end if;
    if(new.remark!=old.remark)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donee',new.ID,'update','remark',old.remark,new.remark);
        end if;
    if(new.avaliable!=old.avaliable)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'donee',new.ID,'update','avaliable',cast(old.avaliable as char),cast(new.avaliable as char));
        end if;
end$

create trigger I_keeper
after insert on keeper for each row
begin
    insert into logsp (timing,tablename,itemaim,action)
    values(now(),'keeper',new.ID,'insert');
end$

create trigger U_keeper
after update on keeper for each row
begin
    if(new.name!=old.name)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'keeper',new.ID,'update','name',old.name,new.name);
        end if;
    if(new.email!=old.email)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'keeper',new.ID,'update','email',old.email,new.email);
        end if;
    if(new.phone!=old.phone)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'keeper',new.ID,'update','phone',old.phone,new.phone);
        end if;
    if(new.remark!=old.remark)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'keeper',new.ID,'update','remark',old.remark,new.remark);
        end if;
    if(new.avaliable!=old.avaliable)
        then insert into logsp (timing,tablename,itemaim,action,actionaim,bef,aft)
        values(now(),'keeper',new.ID,'update','avaliable',cast(old.avaliable as char),cast(new.avaliable as char));
        end if;
end$