Обсуждение: Update columns in same table from update trigger?
Here's what I want to do:
I have a master table that has 2 columns: idcol1 and idcol2, where idcol2 is equivalent to idcol1
Table: color_eq
idcol1 idcol2
1 1
2 2
2 3
Table: warehouse
idcol qty
1 10
2 20
if I execute "update warehouse set qty=10 where idcolor=3", I want the trigger to search table color_eq for idcol2=3, picks its corresponding idcol1 and update the table warehouse with idcol1.
The problem I'm facing is that the trigger before update won't execute if there isn't a row with idcol=3 in the table warehouse.
Here's my code:
CREATE OR REPLACE FUNCTION update_warehouse() returns "trigger" AS '
declare idcolmaestro float:=0;
BEGIN
select into a idcolor1 from color_eq where idcolor2=old.idcolor;
if a is null then
a=old.idcolor;
end if;
new.idcolor=a;
return new;
END;
' LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER update_warehouse_trigger
before UPDATE
ON warehouse
FOR EACH ROW
EXECUTE PROCEDURE update_warehouse();
Any help would be greatly appreciated!declare idcolmaestro float:=0;
BEGIN
select into a idcolor1 from color_eq where idcolor2=old.idcolor;
if a is null then
a=old.idcolor;
end if;
new.idcolor=a;
return new;
END;
' LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER update_warehouse_trigger
before UPDATE
ON warehouse
FOR EACH ROW
EXECUTE PROCEDURE update_warehouse();
On 22 Jul 2011, at 22:02, Pablo Romero Abiti wrote: > The problem I'm facing is that the trigger before update won't execute if there isn't a row with idcol=3 in the table warehouse. > > Here's my code: > > CREATE OR REPLACE FUNCTION update_warehouse() returns "trigger" AS ' > declare idcolmaestro float:=0; > BEGIN > select into a idcolor1 from color_eq where idcolor2=old.idcolor; You didn't declare 'a'. Perhaps you meant to use idcolmaestro? > if a is null then Try 'if NOT FOUND' here instead. > a=old.idcolor; > end if; > > new.idcolor=a; > return new; > END; > ' LANGUAGE 'plpgsql' VOLATILE; I'd probably change this code a bit so that the assignment to idcolor only takes place if a value with idcolor2=old.idvaluewas found: if FOUND then new.idcolor := a; endif return new; That saves a few unnecessary CPU cycles. Alban Hertroys -- The scale of a problem often equals the size of an ego. !DSPAM:737,4e2a8d6912091302916282!
Hi Alban, thank's for your reply. I already changed it as you wrote, but I'm still having the problem that the trigger won't execute unless the value specified for idcolor in table warehouse does exist. Shouldn't the trigger execute BEFORE the update process?
Regards,
Pablo
De : Alban Hertroys <dalroi@solfertje.student.utwente.nl>
À : Pablo Romero Abiti <pabiti@yahoo.com>
Cc : "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Envoyé le : Samedi 23 Juillet 2011 3h59
Objet : Re: [GENERAL] Update columns in same table from update trigger?
On 22 Jul 2011, at 22:02, Pablo Romero Abiti wrote:
> The problem I'm facing is that the trigger before update won't execute if there isn't a row with idcol=3 in the table warehouse.
>
> Here's my code:
>
> CREATE OR REPLACE FUNCTION update_warehouse() returns "trigger" AS '
> declare idcolmaestro float:=0;
> BEGIN
> select into a idcolor1 from color_eq where idcolor2=old.idcolor;
You didn't declare 'a'. Perhaps you meant to use idcolmaestro?
> if a is null then
Try 'if NOT FOUND' here instead.
> a=old.idcolor;
> end if;
>
> new.idcolor=a;
> return new;
> END;
> ' LANGUAGE 'plpgsql' VOLATILE;
I'd probably change this code a bit so that the assignment to idcolor only takes place if a value with idcolor2=old.idvalue was found:
if FOUND then
new.idcolor := a;
endif
return new;
That saves a few unnecessary CPU cycles.
Alban Hertroys
--
The scale of a problem often equals the size of an ego.
!DSPAM:1288,4e2a8d7112091115917480!
On 23 Jul 2011, at 16:49, Pablo Romero Abiti wrote: > Hi Alban, thank's for your reply. I already changed it as you wrote, but I'm still having the problem that the triggerwon't execute unless the value specified for idcolor in table warehouse does exist. Shouldn't the trigger executeBEFORE the update process? Ah of course, there is no matching row for the update that you expect to fire the trigger, so it never gets fired (no updatetakes place). I hadn't realised that from your examples. You can't fire a trigger on non-existant data. > De : Alban Hertroys <dalroi@solfertje.student.utwente.nl> > À : Pablo Romero Abiti <pabiti@yahoo.com> > Cc : "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> > Envoyé le : Samedi 23 Juillet 2011 3h59 > Objet : Re: [GENERAL] Update columns in same table from update trigger? > > On 22 Jul 2011, at 22:02, Pablo Romero Abiti wrote: > > > The problem I'm facing is that the trigger before update won't execute if there isn't a row with idcol=3 in the tablewarehouse. > > > > Here's my code: > > > > CREATE OR REPLACE FUNCTION update_warehouse() returns "trigger" AS ' > > declare idcolmaestro float:=0; > > BEGIN > > select into a idcolor1 from color_eq where idcolor2=old.idcolor; > > You didn't declare 'a'. Perhaps you meant to use idcolmaestro? > > > if a is null then > > Try 'if NOT FOUND' here instead. > > > a=old.idcolor; > > end if; > > > > new.idcolor=a; > > return new; > > END; > > ' LANGUAGE 'plpgsql' VOLATILE; > > I'd probably change this code a bit so that the assignment to idcolor only takes place if a value with idcolor2=old.idvaluewas found: > > if FOUND then > new.idcolor := a; > endif > > return new; > > That saves a few unnecessary CPU cycles. > > Alban Hertroys > > -- > The scale of a problem often equals the size of an ego. > > > > > > > > Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4e2bdf4512091401616986!
On 24 Jul 2011, at 11:00, Alban Hertroys wrote: > You can't fire a trigger on non-existant data. I just realised the above statement is a little inexact: you can't fire row-level triggers on non-existant data. You can still fire statement-level triggers on them, but with those you won't have information about which row(s) were involved,so it won't help your case. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4e2be09d12091863320979!
On 07/22/2011 11:02 PM, Pablo Romero Abiti wrote:
You have 2 choices:Here's what I want to do:I have a master table that has 2 columns: idcol1 and idcol2, where idcol2 is equivalent to idcol1Table: color_eqidcol1 idcol21 12 22 3Table: warehouseidcol qty1 102 20if I execute "update warehouse set qty=10 where idcolor=3", I want the trigger to search table color_eq for idcol2=3, picks its corresponding idcol1 and update the table warehouse with idcol1.The problem I'm facing is that the trigger before update won't execute if there isn't a row with idcol=3 in the table warehouse.Here's my code:CREATE OR REPLACE FUNCTION update_warehouse() returns "trigger" AS 'Any help would be greatly appreciated!
declare idcolmaestro float:=0;
BEGIN
select into a idcolor1 from color_eq where idcolor2=old.idcolor;
if a is null then
a=old.idcolor;
end if;
new.idcolor=a;
return new;
END;
' LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER update_warehouse_trigger
before UPDATE
ON warehouse
FOR EACH ROW
EXECUTE PROCEDURE update_warehouse();
1) Use a before insert trigger and if the row exists, change it to an update.
2) Use a rule
Sim
Thank you for our help anyway!
De : Alban Hertroys <dalroi@solfertje.student.utwente.nl>
À : Alban Hertroys <dalroi@solfertje.student.utwente.nl>
Cc : Pablo Romero Abiti <pabiti@yahoo.com>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Envoyé le : Dimanche 24 Juillet 2011 4h06
Objet : Re: Re : [GENERAL] Update columns in same table from update trigger?
On 24 Jul 2011, at 11:00, Alban Hertroys wrote:
> You can't fire a trigger on non-existant data.
I just realised the above statement is a little inexact: you can't fire row-level triggers on non-existant data.
You can still fire statement-level triggers on them, but with those you won't have information about which row(s) were involved, so it won't help your case.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:1288,4e2be0a412091498931284!