Re: triggers/storedproc help.....

"dave m" <dmusicman666_at_sympatico.ca> 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)

Just add as where clause to the update
where vid_num = :new.vid_num

I'm sure the datamodel is incorrect though. It doesn't make sense to have one table with a primary key of vid_num and a different table with vid_num and vid_name as primary key, as the vid_name should be functionally dependent from vid_num, or the vid_name column is just denormalized and redundant

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address