SQL PLUS Trigger compilation errors

Question

I am trying to create a trigger to calculate a derived attribute on each insert command. However I am getting compilation errors, I dont know where is the problem.

CREATE OR REPLACE TRIGGER NewTrigger 
BEFORE INSERT 
ON Dates FOR EACH ROW 
 BEGIN 
   SET :NEW.difference := :NEW.date1 - :NEW.date2; 
 END;

Show errors shows me this information:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/7  PL/SQL: SQL Statement ignored
1/11     PL/SQL: ORA-00922: missing or invalid option

There must be something you are not telling us, because that should work. — user330315
– user330315, Commented Dec 18, 2012 at 14:18

wolφi · Accepted Answer · 2012-12-18 14:28:11Z

It's not the trigger, it's the data type. If you substract a date from another date, the result is an interval, not another date:

CREATE TABLE dates (date1 DATE, date2 DATE, datediff DATE, numdiff NUMBER);
INSERT INTO dates (date1, date2) VALUES (sysdate, sysdate-1);

UPDATE dates SET numdiff = date1 - date2;
1 rows updated

UPDATE dates SET datediff = date1 - date2;
SQL Error: ORA-00932: inconsistent datatypes: expected DATE got DATE JULIAN

So, if the trigger stores the interval in a number, it compiles:

CREATE OR REPLACE TRIGGER newtriggernum
  BEFORE INSERT ON dates FOR EACH ROW
BEGIN
  :new.numdiff := :new.date1 - :new.date2;
END;
/
TRIGGER NEWTRIGGERNUM compiled

and if it stores the interval in a date, it doesn't:

CREATE OR REPLACE TRIGGER newtriggerdate
  BEFORE INSERT ON dates FOR EACH ROW
BEGIN
  :new.datediff := :new.date1 - :new.date2;
END;
/
Error(2,11): PL/SQL: ORA-00922: missing or invalid option

Randy · Accepted Answer · 2012-12-18 13:42:57Z

0

CREATE OR REPLACE TRIGGER NewTrigger
BEFORE INSERT ON Dates FOR EACH ROW
BEGIN 
    :NEW.difference := :NEW.date1 - :NEW.date2;
End;
/

answered Dec 18, 2012 at 13:42

Randy

16.7k2 gold badges40 silver badges56 bronze badges

1 Comment

wolφi Over a year ago

No, that doesn't compile either with the same error message :-(

Collectives™ on Stack Overflow

SQL PLUS Trigger compilation errors

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related