-
Notifications
You must be signed in to change notification settings - Fork 1
/
queries.txt
126 lines (81 loc) · 4.06 KB
/
queries.txt
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
CREATE TABLE adress (aid int(3),
mobile int(10),
pincode varchar(10),
city varchar(20),
state varchar(20),
PRIMARY KEY(aid));
CREATE TABLE catagoery(cid int(3),cname varchar(20),PRIMARY KEY(cid));
CREATE TABLE limages(id int(5) NOT NULL AUTO_INCREMENT,
url longtext NOT NULL ,primary key(id));
CREATE TABLE fimages(id int(5) NOT NULL AUTO_INCREMENT,
url longtext NOT NULL ,primary key(id));
create TABLE fthings(id int(5),
catagoery VARchar(20),
discription VARchar(100),
adressid int(3),
pincode int(10),
uemail varchar(20),
imgid int(5),
PRIMARY KEY(id),
FOREIGN KEY(uemail) REFERENCES user(email),
FOREIGN KEY (adressid) REFERENCES adress(aid)
,FOREIGN KEY (imgid) REFERENCES fimages(id));
create TABLE lthings(id int(5),
catagoery VARchar(20),
discription VARchar(100),
adressid int(3),
pincode int(10),
uemail varchar(20),
imgid int(5),
PRIMARY KEY(id),
FOREIGN KEY(uemail) REFERENCES user(email),
FOREIGN KEY (adressid) REFERENCES adress(aid)
,FOREIGN KEY (imgid) REFERENCES limages(id));
trigger
CREATE TRIGGER `lcount` AFTER INSERT ON `lthings` FOR EACH ROW
UPDATE `user` SET `posts`=`posts`+1 WHERE `email`=new.`uemail`;
CREATE TRIGGER `fcount` AFTER INSERT ON `fthings` FOR EACH ROW
UPDATE `user` SET `posts`=`posts`+1 WHERE `email`=new.`uemail`
CREATE TRIGGER `lostpostdel` AFTER DELETE ON `lthings` FOR EACH ROW
UPDATE `user` SET `posts`=`posts`-1 WHERE `email`=old.`uemail`
CREATE TRIGGER `foundpostdel` AFTER DELETE ON `fthings` FOR EACH ROW
UPDATE `user` SET `posts`=`posts`-1 WHERE `email`=old.`uemail`
CREATE TRIGGER `lostpostdel` AFTER DELETE ON `lthings` FOR EACH ROW BEGIN
UPDATE `user` SET `posts`=`posts`-1 WHERE `email`=old.`uemail`;
DELETE FROM `limages` WHERE `id`=old.`imgid`;
DELETE FROM `adress` WHERE `aid`=old.`adressid`;
END
CREATE TRIGGER `foundpostdel` AFTER DELETE ON `fthings` FOR EACH ROW BEGIN
UPDATE `user` SET `posts`=`posts`-1 WHERE `email`=old.`uemail`;
DELETE FROM `fimages` WHERE `id`=old.`imgid`;
DELETE FROM `adress` WHERE `aid`=old.`adressid`;
END
CREATE TRIGGER `userdel` AFTER DELETE ON `user` FOR EACH ROW BEGIN
DELETE FROM `fthings` WHERE `uemail`=old.`email`;
DELETE FROM `lthings` WHERE `uemail`=old.`email`;
END
////
CREATE TRIGGER `fcount` AFTER INSERT ON `fthings`
FOR EACH ROW UPDATE `user` SET `posts`=`posts`+1 WHERE `email`=new.`uemail`
CREATE TRIGGER `foundpostdel` AFTER DELETE ON `fthings`
FOR EACH ROW BEGIN
UPDATE `user` SET `posts`=`posts`-1 WHERE `email`=old.`uemail`;
DELETE FROM `fimages` WHERE `id`=old.`imgid`;
DELETE FROM `adress` WHERE `aid`=old.`adressid`;
END
CREATE TRIGGER `userdel` AFTER DELETE ON `user`
FOR EACH ROW BEGIN
DELETE FROM `fthings` WHERE `uemail`=old.`email`;
DELETE FROM `lthings` WHERE `uemail`=old.`email`;
END
CREATE TRIGGER `lcount` AFTER INSERT ON `lthings`
FOR EACH ROW UPDATE `user` SET `posts`=`posts`+1 WHERE `email`=new.`uemail`
CREATE TRIGGER `lostpostdel` AFTER DELETE ON `lthings`
FOR EACH ROW BEGIN
UPDATE `user` SET `posts`=`posts`-1 WHERE `email`=old.`uemail`;
DELETE FROM `limages` WHERE `id`=old.`imgid`;
DELETE FROM `adress` WHERE `aid`=old.`adressid`;
END
CREATE DEFINER=`root`@`localhost` PROCEDURE `userCount`(OUT `counts` INT)
NO SQL
select count(*) into counts from user