Re: update in triggers

[Alban Hertroys <alban@xxxxxxxxxxxxxxxxx>]

Jamie Deppeler wrote:
> Trigger
> CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE
> ON "chargeratetest" FOR EACH ROW
> EXECUTE PROCEDURE "chargeratetest"();
>
>
> function
>
> CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS'
> begin
>
>  UPDATE chargeratetest
>  set notes=''hello''
>  where new."primary" = chargeratetest."primary";
>
>  return null;
> end;
> 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

If you're only going to modify the updated/inserted record, you should 
definitely take a look at RULEs (Chapter 34). They RULE for this kind of 
thing ;)

I haven't used them yet, as I only knew about triggers until recently, 
but you could do something like this:

CREATE RULE new_rule AS ON UPDATE
    TO chargeratetest
    DO INSTEAD
	UPDATE chargeratetest
	   SET notes = 'hello'
	 WHERE primary = NEW.primary;

Alban.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

              http://archives.postgresql.org