Hi all
I am afraid, I have not understood the rule system yet.
I have got 4 tables and a view over all of them.
ladevorgaenge kanton tarifgruppe
0..1 \ | /
\ | /
\ | /
| | |
/|\ /|\ /|\
tarif_progressiv
An DML onto the view tarif_progressiv_denorm is not possible because of
the joins. So I intended to create rules to handle that. However, the do
not behave as I expect.
INSERT INTO
"budget"."tarif_progressiv_denorm"
(
"kantonscode",
"kantonsname",
"tarifgruppencode",
"kinder_anzahl",
"kirchensteuer_flag",
"einkommen_steuerbares_range_low_boundary",
"einkommen_steuerbares_range_high_boundary",
"tarifschritt",
"mindeststeuer",
"steuersatz",
"ladevorgaenge⠒id"
)
VALUES
(
'AG',
null,
'M',
1,
false,
10,
30,
10,
10,
20,
'0562b97a-87af-4071-b56d-f25b4e9bca0f'
);
a) Unexpected not-null constraint violationfor kanton⠒id
update tarif_progressiv_denorm
set kantonscode = 'BE'
where kantonscode = 'AG';
update tarif_progressiv_denorm
set kantonsname = 'Bern'
where kantonsname = 'Aargau';
update tarif_progressiv_denorm
set kantonsname = 'Zürich'
where kantonscode = 'AG';
b) I thought the following would throw a not-null constraint
violationbecause kanton⠒id must not be null.
update tarif_progressiv_denorm
set kantonscode = null
where kantonscode = 'AG';
c) I noticed that, even though the rules define logic for the other
attributes, those do not get changed if not present in an update. While
this is actually good, but surprises me nonetheless.
Did I miss some reading in the doc? Must I use triggers instead?
Btw, I am using DbVis against PostgreSQL 16 for the queries.
Kind regards
Thiemo