| Create a trigger that is triggered by an UPDATE [message #206963] |
Sat, 02 December 2006 13:14  |
jltt
Messages: 1
Registered: December 2006
|
Junior Member |
|
|
THis is my scenerio,
I have created an MM_RENTAL table. When a rented movie is returned, the rental record is not removed from the MM_RENTAL table, but rather the CHECKIN_DATE in the row for that rental record is set to SYSDATE. In the procedure I created called MOVIE_RETURN_SP that is contained in the package MM_RENTALS_PKG an UPDATE of CHECKIN_DATE in MM_RENTAL is followed by an UPDATE to MM_MOVIE that adds one to the MOVIE_QTY value for the movie that is returned. However, it would be advantageous to have the increment of MOVIE_QTY in the MM_MOVIE table done automatically with a database trigger.
Thus I need to create a trigger that is triggered by an UPDATE of CHECKIN_DATE column in MM_RENTAL table. The trigger should update table MM_MOVIE by adding one to the value of MOVIE_QTY in the row that has the same MOVIE_ID as the updated row in the MM_RENTAL table
Anybody can help me with this?
Thanks alot
|
|
|
|
| Re: Create a trigger that is triggered by an UPDATE [message #206966 is a reply to message #206963] |
Sat, 02 December 2006 14:25  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Trigger is just another PL/SQL procedure, stored in a database (just like your package and other procedures you wrote). It seems that you know what to do, you know how to write a PL/SQL procedure, so the only thing you don't have is a CREATE TRIGGER syntax (which can easily be found in the Documentation or elsewhere (Google, for example)).
As you are reading one table (which will have a trigger attached to it) and updating another one, you shouldn't have any problems (regarding mutating tables).
So, after you try it yourself, do come back and tell us what you've done and what happened - does it work properly or not. Then we'll see what next.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
