Oracle SQL trigger - DBMS_OUTPUT.PUT_LINE

Question

I'm creating a trigger within my database, I came across two error that I am not able to fix, I'm pretty sure that those two are relating to my use of DBMS_OUTPUT.PUT_LINE, the rest of the statement does not cause any errors, although it had before.

Errors:

Error(5,3): PL/SQL: SQL Statement ignored

Error(5,15): PL/SQL: ORA-00903: invalid table name

Code:

CREATE TRIGGER INVOICES
BEFORE INSERT OR UPDATE ON BRUINVOICE
FOR EACH ROW 
BEGIN  
  IF :new.BRU_DATE < :new.BRU_PAID_DATE THEN
  DBMS_OUTPUT.PUT_LINE('You cannot do that');
  ELSE
  INSERT INTO table BRUINVOICE
  values 
  from inserted;
END IF;
END;

Your `INSERT` doesn't make sense. There is no `table` keyword in an `INSERT` statement, it seems unlikely that you've created a table named `inserted` to select from and if you are intending to select from another table, you'd need an `INSERT ... SELECT`. But since you're apparently inserting into the same table that your trigger is defined on, I'm hard-pressed to imagine what you are trying to accomplish. Maybe you want to raise an error rather than writing to `dbms_output` and remove the `ELSE` clause entirely? — Justin Cave, Jan 27 '15 at 20:29

score 3 · Accepted Answer · answered Jan 27 '15 at 21:30

Check constraints are a better choice (performance-wise) than triggers when it comes to record level validation:

ALTER TABLE bruinvoice
ADD CONSTRAINT validate_bru_date CHECK (BRU_DATE < BRU_PAID_DATE);

Inserting invalid data will raise an error message like the following:

scott@ORCL> insert into bruinvoice values ('21-DEC-14','20-DEC-14');
insert into bruinvoice values ('21-DEC-14','20-DEC-14')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.VALIDATE_BRU_DATE) violated

score 3 · Answer 2 · answered Jan 28 '15 at 07:13

I fully agree with cstotzer, a check constraint is much better in your situation at should be the preferred way of doing it. However, just for information this would be the trigger syntax:

CREATE TRIGGER INVOICES
BEFORE INSERT OR UPDATE ON BRUINVOICE
FOR EACH ROW 
BEGIN  
  IF :new.BRU_DATE < :new.BRU_PAID_DATE THEN
     RAISE_APPLICATION_ERROR(-20001, 'You cannot do that');
  END IF;
END;

You don't need any ELSE, your INSERT or UPDATE will be simply executed in this case.

Oracle SQL trigger - DBMS_OUTPUT.PUT_LINE

2 Answers2