Trigger to update column based on column of same table and column of other table

Hi earthman2,

I tried your suggestion but the trigger simply does not 'trigger' .
There are no changes at all.

I did also change the type of the_garment_price into float to match it with the type of
the column which is being updated but no luck.

CREATE TRIGGER style_garment_price_trigger BEFORE UPDATE OR INSERT ON style
    FOR EACH ROW EXECUTE PROCEDURE style_garment_price();

http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html

Row-level triggers fired BEFORE may return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value. Returning a row value different from the original value of NEW alters the row that will be inserted or updated (but has no direct effect in the DELETE case). To alter the row to be stored, it is possible to replace single values directly in NEW and return the modified NEW, or to build a complete new record/row to return.

The return value of a BEFORE or AFTER statement-level trigger or an AFTER row-level trigger is always ignored; it may as well be null. However, any of these types of triggers can still abort the entire operation by raising an error.

>http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html

I did actually use the examples of this page to build my previous working triggers

let me show the relevant code I am using:
-------------------------------------------------------------------------------------------------------
create table STYLE(
style_No varchar(20) PRIMARY KEY,
style_Name varchar (20) NOT NULL,
style_Type varchar (20),
pattern_No int,
packaging_Price float,
making_price float default 0.0,
guide_MarkUp float
);

create table ITEM(
item_No varchar (20) PRIMARY KEY,
item_Name varchar (20) NOT NULL,
quantity_4_Garment float,
base_Quality varchar (20),
width float,
composition varchar (40),
price_4_garment float,
style_No varchar(20) REFERENCES STYLE (style_no) ON UPDATE CASCADE ON DELETE SET NULL,
supplier varchar(20) REFERENCES SUPPLIER(supp_ID) ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE FUNCTION style_garment_price() RETURNS trigger AS $style_garment_price$

DECLARE
  the_garment_price  float := 0.0;
BEGIN
  select sum( i.price_4_garment )
  into the_garment_price
  from item i
  where i.style_no = NEW.style_no;
  NEW.making_price := NEW.packaging_price + the_garment_price;
  RETURN NEW;
END;
$style_garment_price$ LANGUAGE plpgsql VOLATILE;
 
 
CREATE TRIGGER style_garment_price AFTER UPDATE OR INSERT ON style
    FOR EACH STATEMENT EXECUTE PROCEDURE style_garment_price();
   
------------------------------------------------------------------------------------------------------------------
then after executing the first insert statement on table style

insert into STYLE values ('wall3456','adriano','trousers',20,1.25,DEFAULT,0.5)


I get this error message :


"ERROR:  record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function "style_garment_price" line 5 at select into variables"

hi earthman2,

The trigger works and for that you will get at least the initial 125 points of the question.

Now I am realising that the scenario is a bit more complex from what I expained initially
For that I have incresed the points from 125 to 225.

The purpose of the initial trigger was to keep the value of making_price up to date.
The trigger given by earthman2 takes care of the case when the style was changing.

but making_price depends on one value from style relation and an other value from item relation.

So I guess there should be an other trigger which takes care of the case when the item relation changes.

I did have a go at it creating this new trigger but it doesn't seem to work.
 

CREATE FUNCTION update_style() RETURNS trigger AS $$

BEGIN

update style set making_price = making_price + new.price_4_garment
where style_no = new.style_no;

RETURN NEW;
END;
$$ LANGUAGE plpgsql ;


CREATE TRIGGER update_style AFTER UPDATE ON ITEM
    FOR EACH STATEMENT EXECUTE PROCEDURE update_style();

You still are not getting the significance of FOR EACH ROW.  The context of NEW / OLD does not exist otherwise.

CREATE TRIGGER update_style AFTER UPDATE ON ITEM
    FOR EACH ROW EXECUTE PROCEDURE update_style();

You need to ask why you are doing this in the first place ?

Why not create a VIEW that defines the making_price column for you.

create view STYLE_VIEW as select s.style_No, s.style_Name, s.style_Type, s.pattern_No, s.packaging_Price,
s.packaging_Price + i.price_4_garment as making_price, s.guide_MarkUp from style s, item i where s.style_no = i.style_no;

As soon as data changes in one column then it is reflected in the view immediately.

>You still are not getting the significance of FOR EACH ROW
Sorry,
I did not notice the FOR EACH STATEMENT
As the old saying says: " you look with your mind, not with your eyes"

Yes the second trigger works too.

>Why not create a VIEW that defines the making_price column for you.

I do actually like the Idea of the views in this case.
Though there is a slight problem with the given view: It only gives those instances of STYLE which have got a related instance in ITEM, these is caused by the condition s.style_no = i.style_no.

So all the STYLE instances which do not have a corresponding instance in ITEM where s.style_no = i.style_no. it wont appear in the view.

How can I make them appear in the view ?

Thanks a lot

create view STYLE_VIEW as select s.style_No, s.style_Name, s.style_Type, s.pattern_No, s.packaging_Price,
s.packaging_Price + i.price_4_garment as making_price, s.guide_MarkUp from style s left join item i using(style_no);

Yes the left join...

We are almost there.

I do get spurious tuples thought:

"wall3456";"adriano";"trousers";"20";"1.25";"";"0.5"
"wall3456";"adriano";"trousers";"20";"1.25";"2.05";"0.5"

Hope your patience is not running out,
:-)

create view STYLE_VIEW as select s.style_No, s.style_Name, s.style_Type, s.pattern_No, s.packaging_Price,
s.packaging_Price + coalesce( i.price_4_garment,  0.0 ) as making_price, s.guide_MarkUp from style s left join item i using(style_no);

For some reason I get:

"wall3456";"adriano";"trousers";"20";"1.25";"1.25";"0.5"
"wall3456";"adriano";"trousers";"20";"1.25";"2.05";"0.5"

I was expecting only
"wall3456";"adriano";"trousers";"20";"1.25";"2.05";"0.5"

For each insertion of ITEM  I get a new
instance in the view

That is what happens when you join tables !