Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesOracle Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old April 29th, 2005, 12:51 PM
syumayip syumayip is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Apr 2005
Posts: 1 syumayip User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 40 m 29 sec
Reputation Power: 0
Question [help]oracle trigger problem

hi all,
i am a newbie of oracle. i am now doing a assignment and i cant find the way the slove the problem.
the problem is taht:

i am required to create a trigger fired by an update statement. it will decide the tuple can be updated or not based on another table. but i cant find way to get the detail of the updated tuple.

the following is some part of my assigment:

create table movie (
mname VARCHAR2(50) primary key,
year number(4) not null,
did char(1) references director(did),
d_pay number(10,2) constraint check_d_pay check(d_pay<1000000)
);

create table sequel (
original_name VARCHAR2(50) PRIMARY KEY references movie(mname),
sequel_name VARCHAR2(50) NOT NULL
);

when update the d_pay, such as:
update movie set d_pay = 5000 where mname = 'troy';

the trigger are required to check if the movie has sequel or not. if it has sequel, d_pay are not allow to be updated.

can anyone teach me how to reach that purpose??
thx

Reply With Quote
  #2  
Old May 1st, 2005, 04:40 PM
paulh1983 paulh1983 is offline
Contributing User
Dev Shed Beginner (1000 - 1499 posts)
 
Join Date: Dec 2004
Posts: 1,240 paulh1983 User rank is Sergeant (500 - 2000 Reputation Level)paulh1983 User rank is Sergeant (500 - 2000 Reputation Level)paulh1983 User rank is Sergeant (500 - 2000 Reputation Level)paulh1983 User rank is Sergeant (500 - 2000 Reputation Level)paulh1983 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Week 1 Day 12 h 39 m 20 sec
Reputation Power: 17
Code:
create or replace trigger before UPDATE of D_PAY on MOVIE
for each row
declare
SN varchar2(20);

begin
select sequel_name into SN from sequel where original_name =  :NEW.mname;

if SN = '' THEN
 raise error NO_SEQUEL
End if

Exception
NO_SEQUEL
dbms_output.putline("NO SEQUEL")

end;

needs some improvement but this ought to do it?

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesOracle Development > [help]oracle trigger problem


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway