NiceMan331
asked on
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
it is ok
now want to use trigger to disallow close month if prev is open
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;
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
no , the user is able to change the flag only
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.
ASKER
yes sdstuber , it works
i just forget to add the value of " >0"
thanx
i just forget to add the value of " >0"
thanx
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?