trigger of update

i have table month_close
year number
month number
close_flag number

i created function month_open to check if month is open or not

create or replace FUNCTION Month_Open(yr number,mn number) RETURN INTEGER
 IS
  lDummy INTEGER; 
 BEGIN
   SELECT count(*)
   INTO lDummy
   FROM serial
   where
  year=yr
   AND month = mn;
  
   RETURN lDummy;
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     RETURN 0;
 END month_open;

Open in new window


it is ok
now want to use trigger to disallow close month if prev is open

create or replace
TRIGGER LUCK_ser_month_close 
  after UPDATE of close_flag ON month_close 
  for each row
 
  BEGIN
 
  if month_open(:old.year,:old.month-1) then
  if :new.close_flag = 1 then
  raise_application_error (-20500,'You Cannot ');
     end if;
     end if;
  END;

Open in new window

NiceMan331Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
your function returns a number but you're using it like it returned a boolean


either change the function to return TRUE/FALSE

or change the IF to be something like ...

if month_open(:old.year,:old.month-1) > 0 then
0
 
sdstuberCommented:
also, I see you call the function with OLD values, but check the flag for NEW values


what if the user changed the year and/or month in addition to or instead of the flag?

Should that impact the results?  If so, how?
0
 
NiceMan331Author Commented:
no , the user is able to change the flag only
0
 
awking00Commented:
Does close_flag = 1 mean the month is open or closed? Also, I'm not sure that simply using the year and month minus 1 will be accurate. If month_open is being passed (2013, 1), then month_open(2013,0) is always going to return 0, when I think you want to know if month_open(2012,12) is open or not.
0
 
NiceMan331Author Commented:
yes sdstuber , it works
i just forget to add  the value of " >0"
thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.