Re: triggers/storedproc help.....

dmusicman666_at_sympatico.ca (dave m) wrote in message news:<4c92ebd6.0306251343.31658b8d_at_posting.google.com>...
> hello first off I am taking a course on Database Modelling with little
> emphasis on triggers
> and/or stored procedures so dont percieve that this is doing my homework
> for me :}
> I am just trying to implement a little database for my own learning....
> Ok its a video rental type thing... the two relevent tables are..
> Checkout ... (a bridge table with VID_NUM representing the video
> identification number)
> which is a foriegn key to...
> ForRent ... (Composite Primary key... VID_NUM and VID_NAME..... with an
> attribute
> called VID_NUMINSTOCK which is hopefully self
> describing.... )
> I created a trigger that works ok but with obvious limitations......
>
> RUN
> CREATE OR REPLACE TRIGGER TRG_CHECKOUT_AJUST_STOCK
> AFTER INSERT OR UPDATE OF VID_NUM ON CHECKOUT
>
> BEGIN
> UPDATE FORRENT
> set VID_NUMINSTOCK = VID_NUMINSTOCK - 1;
> END;
>
> The trigger updates all the VID_NUMINSTOCK fields in the ForRent
> table.....
> As you can guess i need only to reduce the VID_NUMINSTOCK where the
> update
> is occuring on only one peticular VID_NUM......
> Hopefully I am explaining my "querry" adaquately (pardon the pun!).....
> If I have not given enough of a description of the table and or details
> please let me know..
> Any help would be greatly appreciated!
> Thx in advance D.Moore (motivated rookie)

Goto http://tahiti.oracle.com and read about DML (Data Manipulation language) SQL... specially about "WHERE" clause.

Hint:
UPDATE foo_table

   SET foo_value = this - that
   WHERE foo_value = ???;

Regards
/Rauf Sarwar Received on Thu Jun 26 2003 - 09:10:35 CDT