Oracle Insert returning ORA-01400

Question

I am attempting the following SQL in an Oracle 11g DB, which returns

SQL Error: ORA-01400: cannot insert NULL into ("CRABERS"."AG_ASSET_REF"."CREATE_ID").

However, you can see that I am populating this column, which is formatted as number(38,0). As I am inserting a value, why else might I be receiving ORA-01400?

INSERT INTO ag_asset_ref
            (asset_type_id,
             create_id,
             create_date,
             file_size,
             bus_unit_id,
             status,
             name)
VALUES      ( 1050,
             2458,
             SYSDATE,
             50000,
             1000,
             0,
             'test insert' )

Triggers? How can a trigger refer the table itself? If it were a trigger on `ag_asset_ref` and this table were to be referred inside the trigger, shouldn't that have thrown a mutating trigger error? (Unless its a `compound` trigger since I see that OP is using 11g) — Anjan Biswas, Aug 13 '12 at 19:33
@Annjawn, a trigger doesn't "refer" to a table, it *is on* a table. e.g. as in Jeff's example below, a `BEFORE INSERT` trigger might be defined on the table `ag_asset_ref` which will fire whenever anything tries to insert into that table. Because it's a `BEFORE` trigger, it can change the actual value inserted, overriding what was provided in the initial insert statement. — Jeffrey Kemp, Aug 15 '12 at 04:57

score 2 · Answer 1 · answered Aug 13 '12 at 19:45

2

With this trigger your error appears:

CREATE TRIGGER t_ag_asset_ref BEFORE INSERT OR UPDATE ON ag_asset_ref
FOR EACH ROW
BEGIN
    :NEW.create_id := NULL;
END;

You case is probably not so simple but a trigger can certainly be the cause.

answered Aug 13 '12 at 19:45

Jeff

736
5
14

Oracle Insert returning ORA-01400

1 Answers1