Re: Lock inside trigger

I'm not sure that postgres will do anything special (regarding locking) on
the other table that you're updating using a trigger.

Append the phrase "FOR UPDATE" in your select statement - this will give
you a row level lock on that table. After you issue the UPDATE statement
(later on) on that same table the lock will be released.

The change to your code should be:

select coalesce(prvsalproc,0) into salproc from
prvdor where prvcode=new.ecp_provcode
for update;

Hope that helps.

John Sidney-Woollett

Josué Maldonado said:
> Hello list,
>
> I have pl/pgsql trigger that before insert a details table it must
> update a column in other table, the trigger have this code:
>
> CREATE OR REPLACE FUNCTION public.tg_ecproc_insert()
> RETURNS trigger AS
> ' DECLARE
> salproc numeric(12,2);
> BEGIN
> select coalesce(prvsalproc,0) into salproc from
> prvdor where prvcode=new.ecp_provcode;
> -- How do I prevent someone else to change
> -- prvdor.prvsalproc during this transaction
> if new.ecp_dc=''C'' then
> new.ecp_saldo = salproc - new.ecp_valor;
> else
> new.ecp_saldo = salproc + new.ecp_valor;
> end if;
> update prvdor set prvsalproc = new.ecp_saldo
> where prvcode=new.ecp_provcode ;
> -- Here I should be able to unlock the row on
> -- prvdor table
> return new;
> END;
> '
> LANGUAGE 'plpgsql' VOLATILE;
>
> Does postgresql automatically handle the lock on tables updated from
> within a trigger? or what's the must appropiate way to get this done?
>
> Thanks in advance,
>
>
> --
>
> Josué Maldonado
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>