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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html