CODEWITHSUNDEEP
oracle-databaseplsqltriggersdatabase-trigger
addy
addy

Reputation: 9

pl/sql to get end date from start date

I'm trying to create a trigger to get value of validity from plan table and add it to the value of startdate to get the enddate into dblog table.I have written this trigger.

My dblog and plan table schema is like this

desc dblog;
 Name                         Null?    Type
 ---------------------------- -------- -------
 PLANID                       NOT NULL NUMBER
 STARTDATE                             DATE
 ENDDATE                      NOT NULL DATE

desc plan;
 Name                         Null?    Type
 ---------------------------- -------- -------
 PLANID                       NOT NULL NUMBER
 VALIDITY                     NOT NULL NUMBER

By default column STARTDATE has SYSDATE value.

CREATE OR REPLACE TRIGGER trg2
BEFORE INSERT ON dblog FOR EACH ROW
DECLARE
   l_startdate date :=  SYSDATE;
   l_enddate date;
   l_validity number;
BEGIN
 SELECT validity INTO l_validity
   FROM plan
  WHERE planid = :NEW.planid;

 l_endate := l_startdate + l_validity;

 SET :NEW.enddate := l_enddate;

END;
/

it shows following error:

10/2     PL/SQL: SQL Statement ignored
10/6     PL/SQL: ORA-00922: missing or invalid option

Am I using wrong concept or what? How can i get this done?

Upvotes: 0

Views: 987

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

l_startdate SYSDATE;

You have not declared the data type of the variable, SYSDATE is an inbuilt function and not a data type.

If you want to assign the value at the time of data type declaration, then do it as:

DECLARE
   l_startdate DATE := SYSDATE;

For example,

SQL> set serveroutput on
SQL> DECLARE
  2    start_dt DATE := SYSDATE;
  3  BEGIN
  4    dbms_output.put_line(start_dt);
  5  END;
  6  /
29-SEP-15

PL/SQL procedure successfully completed.

SQL>

UPDATE OP edited the question

The below query will fail:

SELECT validity INTO l_validity from plan where planid=:new.planid;

since your trigger is defined on dblog table, however, you are referring to the :new values for plan table.

Also,

SET :NEW.enddate := l_enddate;

You don't need the SET clause, simply do it as:

:NEW.enddate := l_enddate;

Upvotes: 3

Related Questions