Thread: how to create such a function??

--- 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/