1 |
-- |
2 |
-- Project: Clipping |
3 |
-- Author: Nelson Ferraz <nferraz@phperl.com> |
4 |
-- Date: 2003-08-14 |
5 |
-- |
6 |
-- More information on triggers: |
7 |
-- http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html |
8 |
-- |
9 |
|
10 |
|
11 |
-- |
12 |
-- STORED PROCEDURE: INSERT CUSTOMER |
13 |
-- |
14 |
|
15 |
CREATE FUNCTION sp_ins_c_customer ( |
16 |
varchar /* c_customer_name */, |
17 |
varchar /* c_customer_phone */, |
18 |
varchar /* c_customer_email */, |
19 |
text /* c_customer_notes */ |
20 |
) |
21 |
RETURNS BOOLEAN |
22 |
AS ' |
23 |
DECLARE |
24 |
p_c_customer_name ALIAS FOR $1; |
25 |
p_c_customer_phone ALIAS FOR $2; |
26 |
p_c_customer_email ALIAS FOR $3; |
27 |
p_c_customer_notes ALIAS FOR $4; |
28 |
|
29 |
BEGIN |
30 |
|
31 |
INSERT INTO c_customer ( |
32 |
c_customer_name, |
33 |
c_customer_phone, |
34 |
c_customer_email, |
35 |
c_customer_notes |
36 |
) VALUES ( |
37 |
p_c_customer_name, |
38 |
p_c_customer_phone, |
39 |
p_c_customer_email, |
40 |
p_c_customer_notes |
41 |
); |
42 |
|
43 |
RETURN TRUE; |
44 |
|
45 |
END; |
46 |
' LANGUAGE 'plpgsql'; |
47 |
|
48 |
|
49 |
-- |
50 |
-- STORED PROCEDURE: UPDATE CUSTOMER |
51 |
-- |
52 |
|
53 |
CREATE FUNCTION sp_upd_c_customer ( |
54 |
int /* p_id */, |
55 |
varchar /* c_customer_name */, |
56 |
varchar /* c_customer_phone */, |
57 |
varchar /* c_customer_email */, |
58 |
text /* c_customer_notes */ |
59 |
) |
60 |
RETURNS BOOLEAN |
61 |
AS ' |
62 |
DECLARE |
63 |
|
64 |
p_id ALIAS FOR $1; |
65 |
p_c_customer_name ALIAS FOR $2; |
66 |
p_c_customer_phone ALIAS FOR $3; |
67 |
p_c_customer_email ALIAS FOR $4; |
68 |
p_c_customer_notes ALIAS FOR $5; |
69 |
|
70 |
BEGIN |
71 |
|
72 |
UPDATE c_customer |
73 |
SET |
74 |
c_customer_name = p_c_customer_name, |
75 |
c_customer_phone = p_c_customer_phone, |
76 |
c_customer_email = p_c_customer_email, |
77 |
c_customer_notes = p_c_customer_notes, |
78 |
c_customer_date_updated = NOW() |
79 |
WHERE |
80 |
c_customer_id = p_id; |
81 |
|
82 |
RETURN TRUE; |
83 |
|
84 |
END; |
85 |
' LANGUAGE 'plpgsql'; |
86 |
|
87 |
|
88 |
-- |
89 |
-- STORED PROCEDURE: DELETE CUSTOMER |
90 |
-- |
91 |
|
92 |
CREATE FUNCTION sp_del_c_customer ( |
93 |
int /* p_id */ |
94 |
) |
95 |
RETURNS BOOLEAN |
96 |
AS ' |
97 |
DECLARE |
98 |
|
99 |
p_id ALIAS FOR $1; |
100 |
|
101 |
BEGIN |
102 |
|
103 |
DELETE |
104 |
FROM c_customer |
105 |
WHERE c_customer_id = p_id; |
106 |
|
107 |
RETURN TRUE; |
108 |
|
109 |
END; |
110 |
' LANGUAGE 'plpgsql'; |
111 |
|
112 |
|
113 |
-- |
114 |
-- STORED PROCEDURE: LOG CUSTOMER |
115 |
-- |
116 |
|
117 |
/* |
118 |
CREATE FUNCTION sp_log_c_customer () RETURNS OPAQUE AS ' |
119 |
BEGIN |
120 |
|
121 |
INSERT INTO log_c_customer ( |
122 |
action, |
123 |
c_customer_name, |
124 |
c_customer_phone, |
125 |
c_customer_email, |
126 |
c_customer_notes |
127 |
) VALUES ( |
128 |
TG_OP, -- insert or update |
129 |
NEW.c_customer_name, |
130 |
NEW.c_customer_phone, |
131 |
NEW.c_customer_email, |
132 |
NEW.c_customer_notes |
133 |
); |
134 |
END; |
135 |
' LANGUAGE 'plpgsql'; |
136 |
|
137 |
CREATE TRIGGER trig_c_customer BEFORE INSERT OR UPDATE ON c_customer |
138 |
FOR EACH ROW EXECUTE PROCEDURE sp_log_c_customer(); |
139 |
*/ |
140 |
|
141 |
|
142 |
|
143 |
-- |
144 |
-- STORED PROCEDURE: INSERT SOURCE |
145 |
-- |
146 |
|
147 |
CREATE FUNCTION sp_ins_c_source ( |
148 |
varchar /* c_source_description */, |
149 |
text /* c_source_notes */ |
150 |
) |
151 |
RETURNS BOOLEAN |
152 |
AS ' |
153 |
DECLARE |
154 |
p_c_source_description ALIAS FOR $1; |
155 |
p_c_source_notes ALIAS FOR $2; |
156 |
|
157 |
BEGIN |
158 |
|
159 |
INSERT INTO c_source ( |
160 |
c_source_description, |
161 |
c_source_notes |
162 |
) VALUES ( |
163 |
p_c_source_description, |
164 |
p_c_source_notes |
165 |
); |
166 |
|
167 |
RETURN TRUE; |
168 |
|
169 |
END; |
170 |
' LANGUAGE 'plpgsql'; |
171 |
|
172 |
|
173 |
-- |
174 |
-- STORED PROCEDURE: UPDATE SOURCE |
175 |
-- |
176 |
|
177 |
CREATE FUNCTION sp_upd_c_source ( |
178 |
int /* p_id */, |
179 |
varchar /* c_source_description */, |
180 |
text /* c_source_notes */ |
181 |
) |
182 |
RETURNS BOOLEAN |
183 |
AS ' |
184 |
DECLARE |
185 |
|
186 |
p_id ALIAS FOR $1; |
187 |
p_c_source_description ALIAS FOR $2; |
188 |
p_c_source_notes ALIAS FOR $3; |
189 |
|
190 |
BEGIN |
191 |
|
192 |
UPDATE c_source |
193 |
SET |
194 |
c_source_description = p_c_source_description, |
195 |
c_source_notes = p_c_source_notes, |
196 |
c_source_date_updated = NOW() |
197 |
WHERE |
198 |
c_source_id = p_id; |
199 |
|
200 |
RETURN TRUE; |
201 |
|
202 |
END; |
203 |
' LANGUAGE 'plpgsql'; |
204 |
|
205 |
|
206 |
-- |
207 |
-- STORED PROCEDURE: DELETE SOURCE |
208 |
-- |
209 |
|
210 |
CREATE FUNCTION sp_del_c_source ( |
211 |
int /* p_id */ |
212 |
) |
213 |
RETURNS BOOLEAN |
214 |
AS ' |
215 |
DECLARE |
216 |
|
217 |
p_id ALIAS FOR $1; |
218 |
|
219 |
BEGIN |
220 |
|
221 |
DELETE |
222 |
FROM c_source |
223 |
WHERE c_source_id = p_id; |
224 |
|
225 |
RETURN TRUE; |
226 |
|
227 |
END; |
228 |
' LANGUAGE 'plpgsql'; |
229 |
|
230 |
|
231 |
-- |
232 |
-- STORED PROCEDURE: LOG SOURCE |
233 |
-- |
234 |
|
235 |
/* |
236 |
CREATE FUNCTION sp_log_c_source () RETURNS OPAQUE AS ' |
237 |
BEGIN |
238 |
|
239 |
INSERT INTO log_c_source ( |
240 |
action, |
241 |
c_source_description, |
242 |
c_source_notes |
243 |
) VALUES ( |
244 |
TG_OP, -- insert or update |
245 |
NEW.c_source_description, |
246 |
NEW.c_source_notes |
247 |
); |
248 |
END; |
249 |
' LANGUAGE 'plpgsql'; |
250 |
|
251 |
CREATE TRIGGER trig_c_source BEFORE INSERT OR UPDATE ON c_source |
252 |
FOR EACH ROW EXECUTE PROCEDURE sp_log_c_source(); |
253 |
*/ |
254 |
|
255 |
|
256 |
|
257 |
-- |
258 |
-- STORED PROCEDURE: INSERT TYPE |
259 |
-- |
260 |
|
261 |
CREATE FUNCTION sp_ins_c_type ( |
262 |
varchar /* c_type_description */, |
263 |
text /* c_type_notes */ |
264 |
) |
265 |
RETURNS BOOLEAN |
266 |
AS ' |
267 |
DECLARE |
268 |
p_c_type_description ALIAS FOR $1; |
269 |
p_c_type_notes ALIAS FOR $2; |
270 |
|
271 |
BEGIN |
272 |
|
273 |
INSERT INTO c_type ( |
274 |
c_type_description, |
275 |
c_type_notes |
276 |
) VALUES ( |
277 |
p_c_type_description, |
278 |
p_c_type_notes |
279 |
); |
280 |
|
281 |
RETURN TRUE; |
282 |
|
283 |
END; |
284 |
' LANGUAGE 'plpgsql'; |
285 |
|
286 |
|
287 |
-- |
288 |
-- STORED PROCEDURE: UPDATE TYPE |
289 |
-- |
290 |
|
291 |
CREATE FUNCTION sp_upd_c_type ( |
292 |
int /* p_id */, |
293 |
varchar /* c_type_description */, |
294 |
text /* c_type_notes */ |
295 |
) |
296 |
RETURNS BOOLEAN |
297 |
AS ' |
298 |
DECLARE |
299 |
|
300 |
p_id ALIAS FOR $1; |
301 |
p_c_type_description ALIAS FOR $2; |
302 |
p_c_type_notes ALIAS FOR $3; |
303 |
|
304 |
BEGIN |
305 |
|
306 |
UPDATE c_type |
307 |
SET |
308 |
c_type_description = p_c_type_description, |
309 |
c_type_notes = p_c_type_notes, |
310 |
c_type_date_updated = NOW() |
311 |
WHERE |
312 |
c_type_id = p_id; |
313 |
|
314 |
RETURN TRUE; |
315 |
|
316 |
END; |
317 |
' LANGUAGE 'plpgsql'; |
318 |
|
319 |
|
320 |
-- |
321 |
-- STORED PROCEDURE: DELETE TYPE |
322 |
-- |
323 |
|
324 |
CREATE FUNCTION sp_del_c_type ( |
325 |
int /* p_id */ |
326 |
) |
327 |
RETURNS BOOLEAN |
328 |
AS ' |
329 |
DECLARE |
330 |
|
331 |
p_id ALIAS FOR $1; |
332 |
|
333 |
BEGIN |
334 |
|
335 |
DELETE |
336 |
FROM c_type |
337 |
WHERE c_type_id = p_id; |
338 |
|
339 |
RETURN TRUE; |
340 |
|
341 |
END; |
342 |
' LANGUAGE 'plpgsql'; |
343 |
|
344 |
|
345 |
-- |
346 |
-- STORED PROCEDURE: LOG TYPE |
347 |
-- |
348 |
|
349 |
/* |
350 |
CREATE FUNCTION sp_log_c_type () RETURNS OPAQUE AS ' |
351 |
BEGIN |
352 |
|
353 |
INSERT INTO log_c_type ( |
354 |
action, |
355 |
c_type_description, |
356 |
c_type_notes |
357 |
) VALUES ( |
358 |
TG_OP, -- insert or update |
359 |
NEW.c_type_description, |
360 |
NEW.c_type_notes |
361 |
); |
362 |
END; |
363 |
' LANGUAGE 'plpgsql'; |
364 |
|
365 |
CREATE TRIGGER trig_c_type BEFORE INSERT OR UPDATE ON c_type |
366 |
FOR EACH ROW EXECUTE PROCEDURE sp_log_c_type(); |
367 |
*/ |
368 |
|
369 |
|
370 |
|
371 |
-- |
372 |
-- STORED PROCEDURE: INSERT NEWS |
373 |
-- |
374 |
|
375 |
CREATE FUNCTION sp_ins_c_news ( |
376 |
int /* c_source_fk */, |
377 |
int /* c_customer_fk */, |
378 |
int /* c_type_fk */, |
379 |
varchar /* c_news_section */, |
380 |
varchar /* c_news_page */, |
381 |
varchar /* c_news_title */, |
382 |
text /* c_news_abstract */, |
383 |
text /* c_news_full_text */ |
384 |
) |
385 |
RETURNS BOOLEAN |
386 |
AS ' |
387 |
DECLARE |
388 |
p_c_source_fk ALIAS FOR $1; |
389 |
p_c_customer_fk ALIAS FOR $2; |
390 |
p_c_type_fk ALIAS FOR $3; |
391 |
p_c_news_section ALIAS FOR $4; |
392 |
p_c_news_page ALIAS FOR $5; |
393 |
p_c_news_title ALIAS FOR $6; |
394 |
p_c_news_abstract ALIAS FOR $7; |
395 |
p_c_news_full_text ALIAS FOR $8; |
396 |
|
397 |
BEGIN |
398 |
|
399 |
INSERT INTO c_news ( |
400 |
c_source_fk, |
401 |
c_customer_fk, |
402 |
c_type_fk, |
403 |
c_news_section, |
404 |
c_news_page, |
405 |
c_news_title, |
406 |
c_news_abstract, |
407 |
c_news_full_text |
408 |
) VALUES ( |
409 |
p_c_source_fk, |
410 |
p_c_customer_fk, |
411 |
p_c_type_fk, |
412 |
p_c_news_section, |
413 |
p_c_news_page, |
414 |
p_c_news_title, |
415 |
p_c_news_abstract, |
416 |
p_c_news_full_text |
417 |
); |
418 |
|
419 |
RETURN TRUE; |
420 |
|
421 |
END; |
422 |
' LANGUAGE 'plpgsql'; |
423 |
|
424 |
|
425 |
-- |
426 |
-- STORED PROCEDURE: UPDATE NEWS |
427 |
-- |
428 |
|
429 |
CREATE FUNCTION sp_upd_c_news ( |
430 |
int /* p_id */, |
431 |
int /* c_source_fk */, |
432 |
int /* c_customer_fk */, |
433 |
int /* c_type_fk */, |
434 |
varchar /* c_news_section */, |
435 |
varchar /* c_news_page */, |
436 |
varchar /* c_news_title */, |
437 |
text /* c_news_abstract */, |
438 |
text /* c_news_full_text */ |
439 |
) |
440 |
RETURNS BOOLEAN |
441 |
AS ' |
442 |
DECLARE |
443 |
|
444 |
p_id ALIAS FOR $1; |
445 |
p_c_source_fk ALIAS FOR $2; |
446 |
p_c_customer_fk ALIAS FOR $3; |
447 |
p_c_type_fk ALIAS FOR $4; |
448 |
p_c_news_section ALIAS FOR $5; |
449 |
p_c_news_page ALIAS FOR $6; |
450 |
p_c_news_title ALIAS FOR $7; |
451 |
p_c_news_abstract ALIAS FOR $8; |
452 |
p_c_news_full_text ALIAS FOR $9; |
453 |
|
454 |
BEGIN |
455 |
|
456 |
UPDATE c_news |
457 |
SET |
458 |
c_source_fk = p_c_source_fk, |
459 |
c_customer_fk = p_c_customer_fk, |
460 |
c_type_fk = p_c_type_fk, |
461 |
c_news_section = p_c_news_section, |
462 |
c_news_page = p_c_news_page, |
463 |
c_news_title = p_c_news_title, |
464 |
c_news_abstract = p_c_news_abstract, |
465 |
c_news_full_text = p_c_news_full_text, |
466 |
c_news_date_updated = NOW() |
467 |
WHERE |
468 |
c_news_id = p_id; |
469 |
|
470 |
RETURN TRUE; |
471 |
|
472 |
END; |
473 |
' LANGUAGE 'plpgsql'; |
474 |
|
475 |
|
476 |
-- |
477 |
-- STORED PROCEDURE: DELETE NEWS |
478 |
-- |
479 |
|
480 |
CREATE FUNCTION sp_del_c_news ( |
481 |
int /* p_id */ |
482 |
) |
483 |
RETURNS BOOLEAN |
484 |
AS ' |
485 |
DECLARE |
486 |
|
487 |
p_id ALIAS FOR $1; |
488 |
|
489 |
BEGIN |
490 |
|
491 |
DELETE |
492 |
FROM c_news |
493 |
WHERE c_news_id = p_id; |
494 |
|
495 |
RETURN TRUE; |
496 |
|
497 |
END; |
498 |
' LANGUAGE 'plpgsql'; |
499 |
|
500 |
|
501 |
-- |
502 |
-- STORED PROCEDURE: LOG NEWS |
503 |
-- |
504 |
|
505 |
/* |
506 |
CREATE FUNCTION sp_log_c_news () RETURNS OPAQUE AS ' |
507 |
BEGIN |
508 |
|
509 |
INSERT INTO log_c_news ( |
510 |
action, |
511 |
c_source_fk, |
512 |
c_customer_fk, |
513 |
c_type_fk, |
514 |
c_news_section, |
515 |
c_news_page, |
516 |
c_news_title, |
517 |
c_news_abstract, |
518 |
c_news_full_text |
519 |
) VALUES ( |
520 |
TG_OP, -- insert or update |
521 |
NEW.c_source_fk, |
522 |
NEW.c_customer_fk, |
523 |
NEW.c_type_fk, |
524 |
NEW.c_news_section, |
525 |
NEW.c_news_page, |
526 |
NEW.c_news_title, |
527 |
NEW.c_news_abstract, |
528 |
NEW.c_news_full_text |
529 |
); |
530 |
END; |
531 |
' LANGUAGE 'plpgsql'; |
532 |
|
533 |
CREATE TRIGGER trig_c_news BEFORE INSERT OR UPDATE ON c_news |
534 |
FOR EACH ROW EXECUTE PROCEDURE sp_log_c_news(); |
535 |
*/ |
536 |
|
537 |
|
538 |
|
539 |
-- END -- |
540 |
|
541 |
-- |
542 |
-- This program is free software; you can redistribute it and/or |
543 |
-- modify it under the terms of the GNU General Public License |
544 |
-- |