cr_trigger_func_waren.sql
3.53 KB
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
-- Triggerfunktionen zu Waren/Warengruppen --
-- [
create function create_valid_produkt_typ_mask()
returns TRIGGER as '
DECLARE
mask bit varying;
freepos int4 := 0;
err_msg text;
new_len int4;
old_len int4;
BEGIN
IF TG_OP <> ''UPDATE'' AND TG_OP <> ''INSERT'' THEN
err_msg := TG_NAME || '': called for wrong action '' || TG_OP;
RAISE EXCEPTION ''%'', err_msg;
END IF;
new_len := bit_length(NEW.produkt_typ);
IF TG_OP = ''UPDATE'' THEN
new_len := bit_length(OLD.konto_typ);
ELSE
old_len := fibu.konto_typ_min_len();
END IF;
IF new_len < old_len THEN
NEW.konto_typ := NEW.konto_typ::BIT(old_len)>>old_len-new_len;
END IF;
IF old_len < new_len THEN
EXECUTE ''update fibu.konten_typen set konto_typ=konto_typ'' ||
''::BIT('' || new_len || '')>>'' || new_len-old_len;
END IF;
RETURN NEW;
END;
' language 'plpgsql';
create function set_tiefe()
returns TRIGGER as '
DECLARE
BEGIN
IF NEW.parent IS NOT NULL THEN
select INTO NEW.tiefe tiefe+1 from
waren.get_warengruppe(NEW.parent);
update waren.warengruppe set is_parent=''true'' where
_id_=NEW.parent;
ELSE
NEW.tiefe := 0;
END IF;
RETURN NEW;
END;
' language 'plpgsql';
create function on_update_delete_check_is_parent()
returns TRIGGER as '
DECLARE
i int4;
BEGIN
select INTO i count(*) from waren.warengruppe where
parent=OLD.parent;
IF i > 0 THEN
update waren.warengruppe set is_parent=''true'' where
_id_=OLD.parent;
ELSE
update waren.warengruppe set is_parent=''false'' where
_id_=OLD.parent;
END IF;
RETURN OLD;
END;
' language 'plpgsql';
create function on_update_check_tiefe()
returns TRIGGER as '
DECLARE
row waren.warengruppe%ROWTYPE;
parent_count int4;
BEGIN
IF NEW.parent IS NULL THEN
NEW.tiefe := 0;
ELSE
IF OLD.parent IS NULL OR
OLD.parent <> NEW.parent THEN
select INTO NEW.tiefe tiefe+1 from
waren.get_warengruppe(NEW.parent);
END IF;
END IF;
IF NEW.tiefe <> OLD.tiefe THEN
FOR row IN select * from waren.warengruppe where
parent=NEW._id_
LOOP
update waren.warengruppe set tiefe=NEW.tiefe+1 where
_id_=row._id_;
END LOOP;
END IF;
RETURN NEW;
END;
' language 'plpgsql';
create function on_delete_new_parent()
returns TRIGGER as '
DECLARE
row waren.warengruppe%ROWTYPE;
BEGIN
FOR row IN select * from waren.warengruppe where
parent=OLD._id_
LOOP
update waren.warengruppe set parent=OLD.parent where
_id_=row._id_;
END LOOP;
RETURN OLD;
END;
' language 'plpgsql';
-- ]
-- Ende Triggerfunktionen zu Waren/Warengruppen --