Oracle trigger compiling, but not updating

Question

I have this trigger:

create or replace 
TRIGGER Trigger_X
before INSERT ON THING
FOR EACH ROW
DECLARE
  V_NEWTHING    VARCHAR2(20);
BEGIN
  NEW_THING('THING'||:old.CDTHING, V_NEWTHING);
  UPDATE THING 
  SET NUMBERTHING = V_NEWTHING
  WHERE :new.CDTHING = :old.CDTHING;
end;

But this code is not working - it does not show errors on compiling, but it won't update THING.

More details: At the line NEW_THING, is the stored procedure. It is returning a V_NEWTHING, that is basically a code to be inserted on the THING. But this code simply does not run, and ORA returns this:

ORA-04092: cannot COMMIT in a trigger ORA-06512: at "MYDATABASE.NEW_THING", line 33 ORA-06512: at "MYDATABASE.Trigger_X", line 4

How can i solve this problem? I am fairly new to Oracle.

You must remove COMMIT from `NEW_THING` procedure. ALternatively don't call the procedure from the trigger. Oracle doesn't permit COMMIT inside triggers. — krokodilko, Dec 13 '13 at 19:28
It worked, but now, it won't save. Am i messing with these old: and news? @kordirko ? — Malavos, Dec 13 '13 at 19:40
If you execute any DML statement: INSERT, UPDATE, DELETE, then you must perform COMMIT to save changes (or ROLLBACK to undo changes). The transaction (client, application) that executes INSERT must do COMMIT. — krokodilko, Dec 13 '13 at 19:45

score 1 · Accepted Answer · answered Dec 13 '13 at 19:41

You have a row-level trigger so you don't want to do DML against the THING table. Just assign the :new.numberting column

new_thing( 'THING' || :old.cdthing, v_newthing );
:new.numberthing := v_newthing'

That assumes, of course, that the goal of your UPDATE statement is to modify the data in the row that is being inserted. If cdthing is not the primary key, you've got a bigger problem.

Oracle trigger compiling, but not updating

1 Answers1