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

RE: Use a rule or a transaction

From: "Madel, Kurt" <KMadel(at)USInspect(dot)com>
To: "'Antoine Reid'" <antoiner(at)hansonpublications(dot)com>, "Madel, Kurt" <KMadel(at)USInspect(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: RE: Use a rule or a transaction
Date: 2000-08-15 19:38:09
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
Hey Antoine,

I am using 7.0.2, and I would be in your debt if you created a working
example.  Basically, the structure of the query I would like to do is as
such (there are six modules in the module table for each quarter:
a_q1,b_q1,c_q1,d_q1,e_q1,morn_q1,a_q2.....).  A student comes to register
and selects all of the classes that are available in a given module, and
then hits submit.  Information will be inserted into the modules table as
follows (in PHP):

$conn=pg_connect(blah, blah, blah)
	or die('Unable to connect to database');
pg_exec($conn,"insert into module (student_id,year) values
 	or die('Unable to insert record, please go back to registration

pg_exec($conn,"begin work");

pg_exec($conn,"update module set a_q1='$a_q1 where student_id=$student_id
and year=$year");

pg_exec($conn,"update class set class.size=class.size+1 where$a_q1
and class.size<class.maxsize");

pg_exec($conn,"commit work");
//and so on for the other 23 module/quarters

If you could give me a working example of a trigger/function that would
simplify this, that would be fantastic.

-----Original Message-----
From: Antoine Reid [mailto:antoiner(at)hansonpublications(dot)com]
Sent: Tuesday, August 15, 2000 3:04 PM
To: Madel, Kurt
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Use a rule or a transaction

On Tue, Aug 15, 2000 at 02:52:24PM -0400, Madel, Kurt wrote:
> Hello Antoine,
> I thought that triggers only worked for one table at a time.  In this
> I want the class.size incremented and checked for maxsize when I perform
> update or insert on the module table.  I was under the impression that a
> trigger would only work if I was performing an insert or update on the
> table that I wanted the trigger to act on.
> Please let me know if I am way off in regards to triggers.
> thanks,
> Kurt


Well, the trigger, basically is a function that will be executed when you
on one table.

If I understand you correctly, you have a table that is a list of classes.
for each class, you want to have a 'reference count' (current usage) and a 
maximum. Trying to take your own words, 'module' is a list of items that are
to be put member of 'classes'. classes is the table that has the maximum and
current usage numbers.

Assuming this is the case, what you do is following: 
(pseudo code.. not actual sql statements!)

on insert on modules, update classes set current_usage=current_usage + 1
the class_id is equal to the id of the row you just added in modules.

on delete on modules, update classes set current_usage=current_usage - 1 ...

on update on modules, IF-and-only-if the class_id changed, increment the new
and decrement the old class.

This all takes care of keeping the refcount in classes up to date.

Now, for the maximum.. since your triggers on modules will always do an
on classes, we can create triggers on update on classes to make sure the
is not over..

create another trigger;

create a trigger AFTER update on classes.  abort the transaction with a
message if class.current_usage > class.maximum.
abort the transaction with proper message if class.current_usage < 0 (this

I have implemented ref counts in the past, I could probably come up with a
example really quick, if you want.  I suspect the maximum check shouldn't be
hard either..

Oh BTW, this should work on 7.0.2, probably on 7.0;  I really don't know
about 6.x.x..

hope this helps

 o          Antoine Reid             o>    Alcohol and calculus   <o>
<|> antoiner(at)hansonpublications(dot)com <|    don't mix. Never drink   |
 >\    antoiner(at)edmarketing(dot)com      >\         and derive.       /<


pgsql-sql by date

Next:From: Antoine ReidDate: 2000-08-15 19:49:13
Subject: Re: Use a rule or a transaction
Previous:From: Jesus AneirosDate: 2000-08-15 19:37:17
Subject: Re: % escape

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