Re: Use a rule or a transaction - Mailing list pgsql-sql
From | Antoine Reid |
---|---|
Subject | Re: Use a rule or a transaction |
Date | |
Msg-id | 20000815154913.A22168@wumpus.lan.edmarketing.com Whole thread Raw |
In response to | RE: Use a rule or a transaction ("Madel, Kurt" <KMadel@USInspect.com>) |
List | pgsql-sql |
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, PRIMARYKEY ("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@hansonpublications.com <| don't mix. Never drink |>\ antoiner@edmarketing.com >\ and derive. /<