Обсуждение: Updating within Triggers

Поиск
Список
Период
Сортировка

Updating within Triggers

От
Jamie Deppeler
Дата:
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"();

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;

Re: Updating within Triggers

От
Jerry Sievers
Дата:
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/