Solved

trigger of update

Posted on 2014-02-09
5
338 Views
Last Modified: 2014-02-10
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

0
Comment
Question by:NiceMan331
  • 2
  • 2
5 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39846567
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39846569
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
 

Author Comment

by:NiceMan331
ID: 39846575
no , the user is able to change the flag only
0
 
LVL 32

Expert Comment

by:awking00
ID: 39848324
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
 

Author Comment

by:NiceMan331
ID: 39849269
yes sdstuber , it works
i just forget to add  the value of " >0"
thanx
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question