RE: Use a rule or a transaction

Hey Antoine,

That is awesome, you just whipped that out, and I think it will work
beautifully. I will let you know.

Also, I think I have the last one licked

- The trigger on INSERT on modules should return an error if the class_id
does not exist..

I created the 'module.class_id' as a foreign key of the class table, so that
won't let a class.id that doesn't exist, to be inserted into the module
table.

Thanks for all your help, I can't wait to get home and test it out,

Kurt

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

On Tue, Aug 15, 2000 at 03:38:09PM -0400, Madel, Kurt wrote:
> 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):

[snip]
I am not really familiar with PHP (installing/admining, but not programming
with it...).. At any rate, you will want to check the success/failure of
every insert/update/delete...

Here is a working example of such tables, including the reference count AND
limit check. This was tested with 7.0.2 on linux, i386. Make sure you have
plpgsql installed in that database....

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

here we go, broken in sections:

# Here, the tables:
# I made those simple for demonstration purposes.

CREATE TABLE "modules" (
"name" character varying(32) NOT NULL,
"class_id" int4 NOT NULL,
PRIMARY KEY ("name")
);

CREATE TABLE "classes" (
"class_id" int4 NOT NULL,
"usage" int4 NOT NULL,
"max" int4 NOT NULL,
PRIMARY KEY ("class_id")
);

# Now, the different plpgsql functions:

# this one will increment usage in a class.
CREATE FUNCTION "increment_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage + 1 where classes.class_id = NEW.class_id;
return NEW;
end;
' LANGUAGE 'plpgsql';

# this one will decrement usage in a class.
CREATE FUNCTION "decrement_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage - 1 where classes.class_id = OLD.class_id;
return OLD;
end;
' LANGUAGE 'plpgsql';

# this one will check if usage is under 0, or over the maximum.
CREATE FUNCTION "check_limit" ( ) RETURNS opaque AS '
begin
IF NEW.usage > NEW.max
then raise exception ''That class is full, sorry.'';
end if;
if NEW.usage < ''0''
then raise exception ''Class cannot have a negative value! Report to the DBA
now!!'';
end if;
return new;
end;
' LANGUAGE 'plpgsql';

# now, let's create triggers to call those functions on some events:
CREATE TRIGGER "insert_modules" AFTER INSERT ON "modules" FOR EACH ROW
EXECUTE PROCEDURE "increment_classes" ();

CREATE TRIGGER "delete_modules" BEFORE DELETE ON "modules" FOR EACH ROW
EXECUTE PROCEDURE "decrement_classes" ();

CREATE TRIGGER "update_classes" AFTER UPDATE ON "classes" FOR EACH ROW
EXECUTE PROCEDURE "check_limit" ();

So there you go.. please note the following:

- The classes table should have a trigger to make sure you don't insert a
row
with a usage != 0.

- There should be a trigger on UPDATE on modules that would decrement the
old
class, and increment the new one. The triggers on classes will already
check
that the new one is not already full..

- The trigger on INSERT on modules should return an error if the class_id
does not exist..

These are left as an exercise to the reader... (especially because i'm not
sure how to do that
last one... :-> )

> Thanks,
> Kurt

hope this helps
antoine

--
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. /<