Solved

trigger of update

Posted on 2014-02-09
5
337 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now