From: Antoine Reid <antoiner@hansonpublications.com>
To: Madel, Kurt <KMadel@USInspect.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Use a rule or a transaction
Date: Tue, 15 Aug 2000 15:03:49 -0400
Message-ID: <20000815150349.A21508@wumpus.lan.edmarketing.com> (raw)
In-Reply-To: <C6F9B91B745CD4119F1500A0C9DD60C02E348F@exchhq01.usinspect.com>
References: <C6F9B91B745CD4119F1500A0C9DD60C02E348F@exchhq01.usinspect.com>
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 case,
> I want the class.size incremented and checked for maxsize when I perform an
> 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 same
> table that I wanted the trigger to act on.
>
> Please let me know if I am way off in regards to triggers.
>
> thanks,
> Kurt
>
Hi!
Well, the trigger, basically is a function that will be executed when you act
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 where
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 class,
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 update
on classes, we can create triggers on update on classes to make sure the maximum
is not over..
create another trigger;
create a trigger AFTER update on classes. abort the transaction with a proper
message if class.current_usage > class.maximum.
abort the transaction with proper message if class.current_usage < 0 (this shouldn't
happen!!!!!)
I have implemented ref counts in the past, I could probably come up with a working
example really quick, if you want. I suspect the maximum check shouldn't be too
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
antoine
--
o Antoine Reid o> Alcohol and calculus <o>
<|> antoiner@hansonpublications.com <| don't mix. Never drink |
>\ antoiner@edmarketing.com >\ and derive. /<
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: antoiner@hansonpublications.com, KMadel@USInspect.com
Subject: Re: Use a rule or a transaction
In-Reply-To: <20000815150349.A21508@wumpus.lan.edmarketing.com>
References: <C6F9B91B745CD4119F1500A0C9DD60C02E348F@exchhq01.usinspect.com> <20000815150349.A21508@wumpus.lan.edmarketing.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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