Home » SQL & PL/SQL » SQL & PL/SQL » Create a trigger that is triggered by an UPDATE
Create a trigger that is triggered by an UPDATE [message #206963] Sat, 02 December 2006 13:14 Go to next message
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 Go to previous message
Littlefoot
Messages: 20894
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.
Previous Topic: Help !! Insert into a table using cursor/
Next Topic: get/create script for existing DB
Goto Forum:
  


Current Time: Mon Dec 05 13:15:02 CST 2016

Total time taken to generate the page: 0.07024 seconds