🚀 go-pugleaf

The code below shows the correct syntax for the SELECT statment. What
do the ? and ?? in the WHERE clauses represent? Are you going to pass
these values in as input parameters? You might find it helpful to read
the chapters on stored procedures and triggers in the Data Definition
Guide.

AS
  DECLARE VARIABLE sum_var float;
begin
  select sum(f2) from table_a
    where f1 = ? into :sum_var;

  update table_b set fb2 = sum_var where fb1 = ??;
end

-- 
Bill Todd (TeamB)

Ok thanks but how I can pass the current record value to the select 
parameter ?

select sum(f2) from table_a
     where f1 = {current record value of table_a)

- Which is better to let the trigger do this or to create a procedure 
and execute it ?

sorry but I cant find an answer in the manual
Thanks

Isaac wrote:

> Ok thanks but how I can pass the current record value to the select
> parameter ?
>
A trigger executes when you make a change to a record in a table. You
can access all of the field values in the record that caused the
trigger to fire using the OLD and NEW context variables. In a before
insert trigger only the NEW values exist; in a before delete trigger
only the OLD values exist; in a before update trigger both the OLD and
NEW values exist. Assuming a before update trigger then OLD.FIELD1 is
the original value in FIELD1 and NEW.FIELD1 is the new value of FIELD1.
This is all explained in the Data Definition Guide.
 
> 
> - Which is better to let the trigger do this or to create a procedure
> and execute it ?

Since I have no idea what you are trying to do or when you are trying
to do it I do not know whether you should use a stored procedure or
trigger.

> 
> sorry but I cant find an answer in the manual

The OLD and NEW context variables are explained on page 10-8 of the
Data Definition Guide.




-- 
Bill Todd (TeamB)

Here is what I am trying to do:
I have a table that will store average cost for an item so I want to sum 
all quantities and divide on total cost for that item, this average cost 
is giving me headache I also need to sum the beginning balance quantity 
and cost on all purchase orders

all this should be done on the fly whenever an item quantity or purchase 
price is changed in any order.

Thanks

Ok I wrote on trigger it fires after updating the table and it works 
good but I need the same calculations to be executed after insert and 
after delete is it ok to make the same trigger exactly 3 times ?

- How this will interfere with other updates done inside the application 
it self ?

Thanks

Isaac wrote:

> Ok I wrote on trigger it fires after updating the table and it works
> good but I need the same calculations to be executed after insert and
> after delete is it ok to make the same trigger exactly 3 times ?

Yes.

> 
> - How this will interfere with other updates done inside the
> application it self ?

It does not interfere with anything. Any time an insert, update or
delete is performed the appropriate trigger will fire. It does not
matter what causes the data to change. The trigger will always execute.


-- 
Bill Todd (TeamB)

can I write one procedure and call it from 3 triggers ?

Isaac wrote:
> can I write one procedure and call it from 3 triggers ?
never mind

?EXECUTE PROCEDURE proc_name 	
[var [, var �]] 	
[RETURNING_VALUES 	
var [, var �]] 	

:)

Isaac wrote:

> can I write one procedure and call it from 3 triggers ?

Yes.

-- 
Bill Todd (TeamB)