cr_trigger_func_konto.sql
5.37 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
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
-- Trigger Funktionen zu Konten --
-- [
create function on_ref_count_null_del_konto()
returns TRIGGER as '
DECLARE
err_msg text;
konto fibu.konto%ROWTYPE;
BEGIN
IF TG_OP <> ''UPDATE'' THEN
err_msg := TG_NAME || '': called for wrong action '' || TG_OP;
RAISE EXCEPTION ''%'', err_msg;
END IF;
IF NEW.used = 0 THEN
select INTO konto * from fibu.konto where kto_nr=NEW.kto_nr;
IF FOUND THEN
delete from fibu.konto where kto_nr=NEW.kto_nr;
END IF;
END IF;
RETURN NEW;
END;
' language 'plpgsql';
create function on_change_kontenplan_check_ranges()
returns TRIGGER as '
DECLARE
kontenplan_row fibu.kontenplan%ROWTYPE;
err_msg text;
BEGIN
IF TG_OP = ''INSERT'' THEN
FOR kontenplan_row IN select * from fibu.kontenplan LOOP
IF (NEW.nr_von >= kontenplan_row.nr_von AND
NEW.nr_von <= kontenplan_row.nr_bis) OR
(NEW.nr_bis >= kontenplan_row.nr_von AND
NEW.nr_bis <= kontenplan_row.nr_bis) OR
(NEW.nr_von < kontenplan_row.nr_von AND
NEW.nr_bis > kontenplan_row.nr_bis) THEN
RAISE EXCEPTION ''range violation'';
END IF;
END LOOP;
RETURN NEW;
END IF;
IF TG_OP = ''UPDATE'' THEN
FOR kontenplan_row IN
select * from fibu.kontenplan where _id_<>NEW._id_
LOOP
IF (NEW.nr_von >= kontenplan_row.nr_von AND
NEW.nr_von <= kontenplan_row.nr_bis) OR
(NEW.nr_bis >= kontenplan_row.nr_von AND
NEW.nr_bis <= kontenplan_row.nr_bis) OR
(NEW.nr_von < kontenplan_row.nr_von AND
NEW.nr_bis > kontenplan_row.nr_bis) THEN
RAISE EXCEPTION ''range violation'';
END IF;
END LOOP;
RETURN NEW;
END IF;
err_msg := TG_NAME || '': called for wrong action '' || TG_OP;
RAISE EXCEPTION ''%'', err_msg;
END;
' language 'plpgsql';
create function on_insert_kontenplan_create_used_konten()
returns TRIGGER as '
DECLARE
err_msg text;
i fibu.kontenplan.nr_von%TYPE;
BEGIN
IF TG_OP <> ''INSERT'' THEN
err_msg := TG_NAME || '': called for wrong action '' || TG_OP;
RAISE EXCEPTION ''%'', err_msg;
END IF;
FOR i IN NEW.nr_von..NEW.nr_bis LOOP
insert into fibu.used_konten (kto_nr, used, konto_typ)
values (i, 0, NEW.konto_typ);
END LOOP;
RETURN NEW;
END;
' language 'plpgsql';
create function on_update_kontenplan_upd_used_konten()
returns TRIGGER as '
DECLARE
err_msg text;
von fibu.kontenplan.nr_von%TYPE;
bis fibu.kontenplan.nr_bis%TYPE;
displacement fibu.kontenplan.nr_von%TYPE;
count fibu.kontenplan.nr_von%TYPE;
i fibu.kontenplan.nr_von%TYPE;
BEGIN
IF TG_OP <> ''UPDATE'' THEN
err_msg := TG_NAME || '': called for wrong action '' || TG_OP;
RAISE EXCEPTION ''%'', err_msg;
END IF;
displacement := NEW.nr_von - OLD.nr_von;
IF (NEW.nr_bis - NEW.nr_von) < (OLD.nr_bis - OLD.nr_von) THEN
count := NEW.nr_bis - NEW.nr_von;
von := OLD.nr_von;
bis := von + count;
FOR i IN von..bis LOOP
update fibu.used_konten set kto_nr=i + displacement,
konto_typ=NEW.konto_typ where kto_nr=i;
END LOOP;
von := bis + 1;
bis := OLD.nr_bis;
FOR i IN von..bis LOOP
delete from fibu.used_konten where kto_nr=i;
END LOOP;
ELSE
von := OLD.nr_von;
bis := OLD.nr_bis;
FOR i IN von..bis LOOP
update fibu.used_konten set kto_nr=i + displacement,
konto_typ=NEW.konto_typ where kto_nr=i;
END LOOP;
von := bis + 1;
bis := NEW.nr_bis;
FOR i IN von..bis LOOP
insert into fibu.used_konten (kto_nr, used, konto_typ)
values (i, 0, NEW.konto_typ);
END LOOP;
END IF;
RETURN NEW;
END;
' language 'plpgsql';
create function on_delete_kontenplan_del_used_konten()
returns TRIGGER as '
DECLARE
err_msg text;
i fibu.kontenplan.nr_von%TYPE;
BEGIN
IF TG_OP <> ''DELETE'' THEN
err_msg := TG_NAME || '': called for wrong action '' || TG_OP;
RAISE EXCEPTION ''%'', err_msg;
END IF;
FOR i IN OLD.nr_von..OLD.nr_bis LOOP
delete from fibu.used_konten where kto_nr=i;
END LOOP;
RETURN OLD;
END;
' language 'plpgsql';
-- ]
-- Ende Trigger --