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

Re: Updating within Triggers

From: Jerry Sievers <jerry(at)jerrysievers(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Updating within Triggers
Date: 2005-10-31 13:50:38
Message-ID: m3fyqhaj81.fsf@prod01.jerrysievers.com (view raw or flat)
Thread:
Lists: pgsql-general
Jamie Deppeler <jamie(at)doitonce(dot)net(dot)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/

In response to

pgsql-general by date

Next:From: VladDate: 2005-10-31 14:30:46
Subject: Re: Starting PostgreSQL 8.0.4 with more memory [FreeBSD
Previous:From: Simon RiggsDate: 2005-10-31 13:34:12
Subject: Re: Starting PostgreSQL 8.0.4 with more memory [FreeBSD

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