This is my scenerio,

In my procedure MOVIE_RENT_SP that is contained in the package MM_RENTALS_PKG an INSERT statement that inserts a row of data into MM_RENTAL table is followed by an UPDATE to MM_MOVIE that decreases by one the MOVIE_QTY value for the movie that is being rented. So, if I could assure that new rows always would be inserted into MM_RENTAL table by calling the MOVIE_RENT_SP everything would be fine. However, nothing prevents the users who have access to the MM_RENTAL table from just executing an INSERT statement from SQL*Plus or iSQL*Plus window. In such case, a new rental record is created without adjusting the available MOVIE_QTY value. I want to resolve this issue by making sure that MM_MOVIE table is automatically updated each time a new row of data is inserted into MM_RENTAL table.

Thus I need to create a trigger that is triggered by an INSERT operation on table MM_RENTAL. The trigger should update table MM_MOVIE by subtracting one from the value of MOVIE_QTY in the row that has the same MOVIE_ID as the new row in the MM_RENTAL table.

Anybody can help me with this?

Thanks alot

First, you do realize that you posted a distinctly Oracle question on an SQL Server forum, don't you? The way triggers are handled in the two RDBMS's are quite different from one another.

That, not with standing, please post the portion of the code that decrements the Movie_Qty column of MM_Movie table so that we might glean such things as what the Primary Key, etc, might be from the code.

--Jeff Moden

Are you an Oracle familar person that wants to find out how to do the equivalent in MS-SQL or did you just post in the MS-SQL room when you ment to post in the Oracle room?