Solved

trigger of update

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

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
UNIX SCP 5 47
Oracle Pivot 2 34
Calculating percentages per course - Oracle Query 3 28
Parametric query in oracle 6 37
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

705 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

12 Experts available now in Live!

Get 1:1 Help Now