pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtriggers on commit
3+ messages / 2 participants
[nested] [flat]
* triggers on commit
@ 2004-03-19 13:04 Erik Thiele <erik@thiele-hydraulik.de>
2004-03-19 15:13 ` Re: triggers on commit Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Erik Thiele @ 2004-03-19 13:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: triggers on commit
2004-03-19 13:04 triggers on commit Erik Thiele <erik@thiele-hydraulik.de>
@ 2004-03-19 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-19 15:27 ` Re: triggers on commit Erik Thiele <erik@thiele-hydraulik.de>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-03-19 15:13 UTC (permalink / raw)
To: Erik Thiele <erik@thiele-hydraulik.de>; +Cc: pgsql-sql
Erik Thiele <erik@thiele-hydraulik.de> writes:
> 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.
I believe "deferred AFTER" triggers are fired just before commit.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: triggers on commit
2004-03-19 13:04 triggers on commit Erik Thiele <erik@thiele-hydraulik.de>
2004-03-19 15:13 ` Re: triggers on commit Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-19 15:27 ` Erik Thiele <erik@thiele-hydraulik.de>
0 siblings, 0 replies; 3+ messages in thread
From: Erik Thiele @ 2004-03-19 15:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Fri, 19 Mar 2004 10:13:56 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Erik Thiele <erik@thiele-hydraulik.de> writes:
> > 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.
>
> I believe "deferred AFTER" triggers are fired just before commit.
i don't find this in the documentation. i only know triggers which run
before insert/update/delete or directly (!) after, not waiting until
commit.
cya & thx!
erik
--
Erik Thiele
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-03-19 15:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-19 13:04 triggers on commit Erik Thiele <erik@thiele-hydraulik.de>
2004-03-19 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-19 15:27 ` Erik Thiele <erik@thiele-hydraulik.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox