triggers on commit

Hi!

I am having a probably stupid question, but anyway I can't find it's
solution. it should be a novice question, but lets see :))

I have 2 tables.

calendar:
person_id INTEGER
when DATE
type TEXT

holiday:
person_id INTEGER
assigned_to DATE
last_possible_assignment DATE

the idea here is that workers have a contingent of holidays that they
can assign in their calendar. this is stored in the holiday table. if
they do not take their holidays, then once they waited until
last_possible_assignment, this holiday is not assignable anymore, i.e.
bad luck for the worker.

the different jobs to do on a certain date
are stored in calendar.type. there are things like "normal_work",
"weekend", "half-time-work" etc. But also there is type "holiday". In
this case there must also be one of the persons holiday days assigned to
this date.

so. if a program sets the type in a calendar table row to "holiday" then
it also must set the assigned_to date in a row of the holiday table to
calendar.when.

now. what i wanted to do is ensure that this is done correctly and
otherwise abort the transaction.

of course this check needs to be done at the commit time, because if i
do it on statement level, it cannot work, since i cannot change two
tables at the same time.

so my first idea was to create a trigger that easily correlates all
entries in calendar with all entries in holiday and checks if everything
is ok. if not, then it raises an exception. i did this in plpgsql. i now
call this trigger on modification of calendar and on modification of
holiday table. but the problem is the trigger is not deferrable until
commit time, since i find no function to do it...

i do not want to create a complicated trigger for update of holiday
table after modification of calendar table. because then i also have to
create a trigger for modification of calendar table after modification
of holiday table. and i want to keep the logic in the client program,
since it is easier encodable there.

all i want to do is an additional check on database layer. is there no
way to call triggers on commit time? even better, triggers just called
if one of two tables was modified, but again at commit time? see, it is
much easier to write a function to check if the database is ok, than it
is to write functions to automatize updates across tables. and again i
want to keep logic in client program to see what is really happening.

my postgresql version is 7.2.1. but i also scanned 7.4 docs for the
wanted functionality.

cya & thx!
erik

--
Erik Thiele