Solved

trigger of update

Posted on 2014-02-09
5
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 74

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 74

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
Suggested Courses

636 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