CODEWITHSUNDEEP
oracle-databaseplsql
raju
raju

Reputation: 209

create pl sql trigger

I am trying to write a Trigger for before insert to validate a case date. The case date should be between 5 years before and 7 years after current date.

For example, in 2018 the case date should be from year 2013 to 2025. If the date is out of range the trigger should stop inserting data.

CREATE OR REPLACE TRIGGER ch
BEFORE INSERT 
on CASE
FOR EACH ROW 
DECLARE

    CASN number;
BEGIN
    SELECT COUNT(*) 
    INTO CASN
    FROM CASE
    WHERE :new.STARTDATE > SYSDATE;
    IF (CASN > 0) THEN
        RAISE_APPLICATION_ERROR(-20000,'Start DATE CANNOT Be GREATER than today's 
date');
END IF; 
END;

Here STARTDATE is column of CASE table

This trigger starts when start date is greater than today's date but I need it to run when it's out range as given above.

How do I add an specified interval to the sysdate, so that it could work for the above condition?

Upvotes: 1

Views: 94

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

The logic you are using in your Trigger is completely wrong. You don't need to get the count from the table using :NEW.STARTDATE. This is something what you are looking for. 

CREATE OR replace TRIGGER ch
  BEFORE INSERT ON cases
  FOR EACH ROW
BEGIN
    IF ( :NEW.casedate < SYSDATE - INTERVAL '5' year
         OR :NEW.casedate > SYSDATE + INTERVAL '7' year ) THEN
      RAISE_APPLICATION_ERROR(-20000,
'CASE DATE should be in range: current date - 5 years and current date + 7 years')
;
END IF;
END;

/

EDIT : I have not added TRUNC on the dates because I'm not sure if you want to consider time component as well while considering date range.If you are ok with just considering days, you may use TRUNC(SYSDATE) in place of just SYSDATE. Modify it accordingly as per your business needs.

Another option is to use CHECK constraint. Although Oracle does not allow you to have use SYSDATE in a check constraint definition, you may create another column( or reuse existing) that defaults to SYSDATE and apply check constraint on that.

ALTER TABLE CASES ADD ( CURR_DATE DATE DEFAULT SYSDATE );

ALTER TABLE CASES ADD CONSTRAINT
RANGE_CHECK CHECK( casedate > CURR_DATE - INTERVAL '5' YEAR
                   AND casedate < CURR_DATE + INTERVAL '7' YEAR) ENABLE;

Upvotes: 3

Anuj Arora
Anuj Arora

Reputation: 21

Date arithmetic. Oracle Database enables you to perform arithmetic operations on dates and time stamps in several ways:

Add a numeric value to or subtract it from a date, as in SYSDATE + 7; Oracle Database treats the number as the number of days.

Add one date to or subtract it from another, as in l_hiredate - SYSDATE.
Use a built-in function to “move” a date by a specified number of months or to another date in a week.

Here are some examples of date arithmetic with a date and a number (assume in all cases that the l_date variable has been declared as DATE):

Set a local variable to tomorrow’s date: 



l_date := SYSDATE + 1;


Move back one hour:



l_date := SYSDATE - 1/24;


Move ahead 10 seconds:



l_date := SYSDATE + 10 / (60 * 60 * 24);

When you add one date to or subtract it from another, the result is the number of days between the two. As a result, executing this block:

    DECLARE
       l_date1   DATE := SYSDATE;
       l_date2   DATE := SYSDATE + 10;
    BEGIN
       DBMS_OUTPUT.put_line (
          l_date2 - l_date1);
       DBMS_OUTPUT.put_line (
          l_date1 - l_date2);
    END;

returns the following output:

10 -10

Upvotes: -1

Related Questions