CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
user9364289
user9364289

Reputation: 11

Error at "dbms_output.put_line"?

I am creating a trigger in Oracle and we are stuggling with the syntax.

I have run it through a code checker several times but it keep saying we have an error at "dbms_output.put_line".

When we try to run it, the code says "trigger created with compilation errors"

CREATE TRIGGER date_trigger
  BEFORE INSERT ON PrintJob
  FOR EACH ROW
BEGIN
  IF StartDate > SysDate THEN 
  BEGIN
    dbms_output.put_line ('Please check date');
  END;

Upvotes: 0

Views: 2153

Answers (1)

Littlefoot
Littlefoot

Reputation: 142715

Trigger can't reference table columns just by naming them - you'll need :old (or :new) pseudorecord qualifiers, such as in this modified example. It is not that DBMS_OUTPUT.PUT_LINE won't work; you wrote it correctly, but should replace it with the RAISE_APPLICATION_ERROR.

SQL> CREATE TABLE printjob (startdate DATE);

Table created.

SQL> CREATE OR REPLACE TRIGGER date_trigger
  2     BEFORE INSERT
  3     ON PrintJob
  4     FOR EACH ROW
  5  BEGIN
  6     IF :new.StartDate > SYSDATE
  7     THEN
  8        -- dbms_output.put_line ('Please check date');
  9        RAISE_APPLICATION_ERROR (-20000, 'Please check date');
 10     END IF;
 11  END;
 12  /

Trigger created.

SQL> INSERT INTO printjob
  2       VALUES (DATE '2018-02-20');
INSERT INTO printjob
            *
ERROR at line 1:
ORA-20000: Please check date
ORA-06512: at "SCOTT.DATE_TRIGGER", line 5
ORA-04088: error during execution of trigger 'SCOTT.DATE_TRIGGER'


SQL>

Upvotes: 2

Related Questions