Thread: how to create such a function??

how to create such a function??

From
"Ireneusz Kramarz"
Date:
Hi there,
I want to create a function and a trigger, but I don't know how...  :(
I need something that would work when I update the table 'stock' , by insert some integer value into 'sold'. Then the integer value in field 'stock' has to be decreased by that inserted value.
 
example of my problem:
 
 -table 'stock' before updating 'sold' field
 
      ticket_id | stock | sold
           1       |   30   |    0
 
 -how table 'stock' should look like after: update stock set sold=2 where bilet_id=1; 
 
     ticket_id | stock | sold
          1       |    28  |    2
 
I have to create something that would work like that, after each update of ticket_id defined rows.
 
I know that you guys can help me with that... apreciate any help which will be priceless  ;-)
 
iron

Re: how to create such a function??

From
Jeff Eckermann
Date:
--- Ireneusz Kramarz <ikramarz@wsb-nlu.edu.pl> wrote:
> Hi there,
> I want to create a function and a trigger, but I
> don't know how...  :(

You need to create a before trigger.  The
documentation on "create trigger" will show you how.

> I need something that would work when I update the
> table 'stock' , by insert some integer value into
> 'sold'. Then the integer value in field 'stock' has
> to be decreased by that inserted value.
>
> example of my problem:
>
>  -table 'stock' before updating 'sold' field
>
>       ticket_id | stock | sold
>            1       |   30   |    0
>
>  -how table 'stock' should look like after: update
> stock set sold=2 where bilet_id=1;
>
>      ticket_id | stock | sold
>           1       |    28  |    2

In your trigger function, you access the old and new
values using the "old" and "new" aliases.  So your
logic would be something like:
IF NEW.sold <> OLD.sold THEN
   NEW.stock := OLD.stock + OLD.sold - NEW.sold
END IF
Look at the "create function" documentation to fill in
the details.
You will need to guard against "stock" and "sold"
being null, perhaps by a table constraint.

__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/