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
– Justin Cave, Commented Jan 27, 2015 at 20:29

cstotzer · Accepted Answer · 2015-01-27 21:30:46Z

3

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

answered Jan 27, 2015 at 21:30

cstotzer

4153 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wernfried Domscheit · Accepted Answer · 2015-01-28 07:13:55Z

3

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.

answered Jan 28, 2015 at 7:13

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

Oracle SQL trigger - DBMS_OUTPUT.PUT_LINE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related