Re: Updating within Triggers

Jamie Deppeler <jamie@doitonce.net.au> writes:

> Hi trying to wtite a trigger to update summary fields in a seperate
> table to do this i am planning on using trigger.  problem i have at
> the moment the update trigger doesnt seem to be fireing but the
> insert works.
>
> Trigger
>
> CREATE TRIGGER "setSummary" AFTER INSERT OR UPDATE
> ON "parts" FOR EACH ROW
> EXECUTE PROCEDURE "material"();

execute procedure material() ?

Your function example below creates a function named partSumm() and it
looks as if you have a table named "material" in the DB.

Perhaps you have by mistake also created a function named material.
don't know on Pg8 but on earlier versions, you can't register a
trigger to a procedure that doesn't exist.

So, your trigger doesn't run the function that you believe it does.

>
> function
> CREATE OR REPLACE FUNCTION "partSumm" () RETURNS trigger AS
> $body$
> begin
>   update project
>       actualsummmatcost = (select sum(actualcost) from projects.material
> where material."fkproject" = new.fkproject;),
>       actualsummmattotal = (select sum(actualcharge) from
> projects.material where material."fkprojet" = new.fkproject;),
>   where project."primary" = new.fkproject;
>   return new;
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

--
-------------------------------------------------------------------------------
Jerry Sievers   305 854-3001 (home)     WWW ECommerce Consultant
                305 321-1144 (mobile    http://www.JerrySievers.com/