· 9 years ago · Feb 01, 2017, 07:04 PM
1use pubs2 --Tell ASE which database to use
2go
3
4/* ****************
5AUFGABE 1
6**************** */
7
8--NEUEN DB USER
9exec sp_addlogin 'sa_web', 'sa_web', @defdb='pubs2' --creates a login for the website team to access the pubs2 db (not actually sure if I need this step)
10exec sp_adduser 'sa_web', 'sa_web', NULL --adds user for the website to access the database
11go
12
13-- GRANT SELECT RECHTE
14--the following grants the user I created SELECT access to the specified tables from problem 1
15grant select on au_pix to sa_web
16grant select on authors to sa_web
17grant select on blurbs to sa_web
18grant select on discounts to sa_web
19grant select on sales to sa_web
20grant select on salesdetail to sa_web
21grant select on stores to sa_web
22grant select on titleauthor to sa_web
23grant select on titles to sa_web
24grant select on roysched to sa_web
25go
26
27
28
29/* ****************
30AUFGABE 2
31**************** */
32---------------------------------------------------------------------------------------------------
33--AUFGABE 2A : POSTALCODE PROCEDURE
34---------------------------------------------------------------------------------------------------
35/* The following creates a procedure. With this procedure the user should be able to enter a postal code (or an incomplete postal code) and look up the stores that way.
36
37in the end they should be able to just enter
38exec postalcode 'postalcodehere', optionalflaghere
39and get information without having to type select statement and stuff like that
40*/
41
42--add error messages so we can call them up later
43sp_addmessage 50001, "angegeber Postalcode wurde nicht gefunden:50001"
44sp_addmessage 50002, "@titles flag hat falschen Wert:50002"
45
46
47create procedure postalcode --creates the procedure and name it postalcode
48 @postalcode varchar(30) = NULL, --sets an input parameter for the user to enter the postal code and fills it with 'NULL' by default (note: that input will still need to be with '' to denote strings since the pubs2 database that we are using has stores.postalcode saved as a char(10)
49 @titles int = 0 --we also create another parameter so that the user can optionally mark the procedure with a '1' in order to indicate that they would like to see the titles sold in the matching stores
50as
51declare @msg varchar(255) --create a variable to hold the error message
52/*
53if the user executes the procedure without any postalcode then we show the user a list of available postal codes in the database
54*/
55 if @postalcode is NULL
56 begin
57 select postalcode from stores --show the user a list of available postalcodes
58 end
59
60/*
61user gives a valid postal code (if the subquery has any matches) and also gives the optional @titles parameter a '1'
62*/
63else if @postalcode is not NULL --user did not leave code blank
64 and --subquery to check if the postal code entered has a match
65 (select count(postalcode) from stores where postalcode like '%'||@postalcode||'%')>0
66 and --user set the titles value to 1 to indicated that they want to see the titles offered
67 @titles = 1
68 begin --then lists the requested info as specified in problem 2a for all matching stores
69 select s.stor_name, s.city, a.au_lname, a.au_fname, a.phone, t.title
70 from authors a
71 left outer join titleauthor ta on ta.au_id=a.au_id
72 left outer join titles t on t.title_id=ta.title_id
73 left outer join salesdetail sd on sd.title_id = t.title_id
74 left outer join stores s on s.stor_id=sd.stor_id
75 where s.postalcode like '%'||@postalcode||'%'
76 end
77/*
78user gives a valid postal code (if the subquery has any matches) but does not give the optional @titles parameter a '1'
79 */
80 else if @postalcode is not NULL --user did not leave code blank and there is a match
81 and --subquery to check if the postal code entered has a match
82 (select count(postalcode) from stores where postalcode like '%'||@postalcode||'%')>0
83 and @titles = 0 --user did not set the titles value to 1 to indicate that they want to see the titles offered
84 begin --then lists the requested info as specified in problem 2a for all matching stores
85 select s.stor_name, s.city, a.au_lname, a.au_fname, a.phone
86 from authors a
87 left outer join titleauthor ta on ta.au_id=a.au_id
88 left outer join salesdetail sd on sd.title_id=ta.title_id
89 left outer join stores s on s.stor_id=sd.stor_id
90 where s.postalcode like '%'||@postalcode||'%'
91 end
92/*
93user gives an invalid code (no match found by the subquery)
94 */
95 else if @postalcode is not NULL and (select count(postalcode) from stores where postalcode like '%'||@postalcode||'%')=0
96 begin --give an error message
97 exec sp_getmessage 50001, @msg output
98 print @msg
99 return (1)
100 end
101/*
102user gives a value to @titles that is not allowed (only 1 is allowed and 0 is the default)
103 */
104else if @titles not in (0, 1)
105 begin --give an error message
106 exec sp_getmessage 50002, @msg output
107 print @msg
108 return (1)
109 end
110go
111--END OF PART 2A
112---------------------------------------------------------------------------------------------------
113
114--grants access to the stored procedure written in problem 2
115grant exec on postalcode to sa_web
116go
117
118---------------------------------------------------------------------------------------------------
119--TEST PART 2A
120---------------------------------------------------------------------------------------------------
121exec postalcode --no postal code given
122exec postalcode '927' --valid postal code
123exec postalcode '927', 1 --valid postal code with @titles flag
124exec postalcode '11',1 --invalid postal code with @titles flag should give an error 50001
125exec postalcode '927',5 --valid code but invalid @titles flag should give an error 50002
126go
127---------------------------------------------------------------------------------------------------
128--AUFGABE 2B
129---------------------------------------------------------------------------------------------------
130/*
131So far everything is working as expected, but for the second part I have to make some changes to the above procedure. But instead of breaking it I am going to just just drop it and recreate it here since ASE does not support ALTER PROCEDURE.
132*/
133
134--drop proc postalcode --so it doesn't interfere
135--go
136
137if exists (select * from #TmpErrorLog)
138 drop table #TmpErrorLog --so it does't interfere
139go
140
141/*
142this temporary table collects all the errors in a separate table however it does not use an output variable as requested in Problem 2B, I am not sure how to apply that in a useful way in this case
143*/
144CREATE TABLE #TmpErrorLog(logdate datetime, msgcode int, postalcode varchar(100), titlesflag int)--table to capture the errors
145go
146
147create procedure postalcode --creates the procedure and name it postalcode
148 @postalcode varchar(30) = NULL, --sets an input parameter for the user to enter the postal code and fills it with 'NULL' by default (note: that input will still need to be with '' to denote strings since the pubs2 database that we are using has stores.postalcode saved as a char(10)
149 @titles int = 0 --we also create another parameter so that the user can optionally mark the procedure with a '1' in order to indicate that they would like to see the titles sold in the matching stores
150as
151declare @msg varchar(255) --create a variable to hold the error message
152declare @msgcode int --I am not sure how else to get the number out of my error message, so I will just create a variable and assign it in each case
153/*
154if the user executes the procedure without any postalcode then we show the user a list of available postal codes in the database
155*/
156 if @postalcode is NULL
157 begin
158 select postalcode from stores --show the user a list of available postalcodes
159 select @msgcode = 0
160 INSERT INTO #TmpErrorLog (logdate,msgcode,postalcode,titlesflag)
161 select getdate(),@msgcode,'@postalcode',@titles --temporary table to capture errors
162 end
163
164/*
165user gives a valid postal code (if the subquery has any matches) and also gives the optional @titles parameter a '1'
166*/
167else if @postalcode is not NULL --user did not leave code blank
168 and --subquery to check if the postal code entered has a match
169 (select count(postalcode) from stores where postalcode like '%'||@postalcode||'%')>0
170 and --user set the titles value to 1 to indicated that they want to see the titles offered
171 @titles = 1
172 begin --then lists the requested info as specified in problem 2a for all matching stores
173 select s.stor_name, s.city, a.au_lname, a.au_fname, a.phone, t.title
174 from authors a
175 left outer join titleauthor ta on ta.au_id=a.au_id
176 left outer join titles t on t.title_id=ta.title_id
177 left outer join salesdetail sd on sd.title_id = t.title_id
178 left outer join stores s on s.stor_id=sd.stor_id
179 where s.postalcode like '%'||@postalcode||'%'
180 select @msgcode = 0
181 INSERT INTO #TmpErrorLog (logdate,msgcode,postalcode,titlesflag)
182 select getdate(),@msgcode,'@postalcode',@titles --temporary table to capture errors
183 end
184/*
185user gives a valid postal code (if the subquery has any matches) but does not give the optional @titles parameter a '1'
186 */
187 else if @postalcode is not NULL --user did not leave code blank and there is a match
188 and --subquery to check if the postal code entered has a match
189 (select count(postalcode) from stores where postalcode like '%'||@postalcode||'%')>0
190 and @titles = 0 --user did not set the titles value to 1 to indicate that they want to see the titles offered
191 begin --then lists the requested info as specified in problem 2a for all matching stores
192 select s.stor_name, s.city, a.au_lname, a.au_fname, a.phone
193 from authors a
194 left outer join titleauthor ta on ta.au_id=a.au_id
195 left outer join salesdetail sd on sd.title_id=ta.title_id
196 left outer join stores s on s.stor_id=sd.stor_id
197 where s.postalcode like '%'||@postalcode||'%'
198 select @msgcode = 0
199 INSERT INTO #TmpErrorLog (logdate,msgcode,postalcode,titlesflag)
200 select getdate(),@msgcode,'@postalcode',@titles --temporary table to capture errors
201 end
202/*
203user gives an invalid code (no match found by the subquery)
204 */
205 else if @postalcode is not NULL and (select count(postalcode) from stores where postalcode like '%'||@postalcode||'%')=0
206 begin --give an error message
207 exec sp_getmessage 50001, @msg output
208 print @msg
209 select @msgcode = 50001 --manually assign this value to the msgcode variable
210 --return (1)
211 INSERT INTO #TmpErrorLog (logdate,msgcode,postalcode,titlesflag)
212 select getdate(),@msgcode,'@postalcode',@titles --temporary table to capture errors
213 end
214/*
215user gives a value to @titles that is not allowed (only 1 is allowed and 0 is the default)
216 */
217else if @titles not in (0, 1)
218 begin --give an error message
219 exec sp_getmessage 50002, @msg output
220 print @msg
221 select @msgcode = 50002 --manually assign this value to the msgcode variable
222 --return (1)
223 INSERT INTO #TmpErrorLog (logdate,msgcode,postalcode,titlesflag)
224 select getdate(),@msgcode,'@postalcode',@titles --temporary table to capture errors
225 end
226go
227
228---------------------------------------------------------------------------------------------------
229--BATCH
230---------------------------------------------------------------------------------------------------
231exec postalcode @postalcode = NULL, @titles = 1--no postal code given
232exec postalcode @postalcode = NULL --no postal code given
233exec postalcode @postalcode = '11'--invalid postal code
234select * from #TmpErrorLog
235go
236----------------------------------------------------------------------------------------------------
237
238/* ****************
239AUFGABE 3
240**************** */
241
242select * into web_titles_backup from titles --create a new table that copies the values over from titles
243
244/*
245All data manipulations should only be executed on the web_titles table. To control the access, the web_titles table should contain two additional columns for the author team (the clients/managers, not the sql programmer) to edit after checking
246*/
247
248alter table web_titles_backup --add the flag columns
249/*
250In the flag_work column the desired desired operations should be used.
251 'N' for normal
252 'D' for 'delete
253 'I 'for' insert
254*/
255add flag_work char(1) default NULL --allow null to make inserting easier
256add flag_edit char(1) default '0' NULL
257
258/*
259As soon as the value is set to '1' in the field flag_edit, the web_titles table should be compared with the titIes table
260 */
261create trigger title_trigger
262on web_titles_backup
263for update,insert
264as
265--if flagged for for inserted rows
266if exists(select * from inserted where flag_work = 'I' and flag_edit = '1') --if there is a value in the inserted column with the I and 1 flags
267 begin
268 insert into titles_backup (title_id, title, type, pub_id, price, advance, num_sold, notes, pubdate, contract) select title_id, title, type, pub_id, price, advance, num_sold, notes, pubdate, contract from inserted
269 end
270--if flagged for deletion
271else if exists(select * from inserted where flag_edit = '1' or flag_work = 'D') and exists (select * from web_titles_backup where flag_work = 'D' and flag_edit = '1')
272 begin
273 delete from web_titles_backup where flag_work = 'D' and flag_edit = '1'
274 delete titles_backup from titles_backup t left join web_titles_backup wt on t.title_id = wt.title_id where wt.title_id is null
275 end