Skip site navigation (1) Skip section navigation (2)

triggers on commit

From: Erik Thiele <erik(at)thiele-hydraulik(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: triggers on commit
Date: 2004-03-19 13:04:45
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql

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.

  person_id INTEGER
  when DATE
  type TEXT

  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

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 Thiele


pgsql-sql by date

Next:From: Martin MarquesDate: 2004-03-19 13:05:40
Subject: Re: psql: FATAL 1: IDENT authentication failed for user error - Urgent pls
Previous:From: KumarDate: 2004-03-19 12:39:33
Subject: psql: FATAL 1: IDENT authentication failed for user error - Urgent pls

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group