Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    9

    Question Unanswered: updateable view with default values

    I have a table TB1 and an updateable view VIEW1 with a trigger INSTEAD_OF_TRIGGER_TB1. The table has 2 columns with "not null" constrains. The 2nd column NAME has an default value. Inserting values into the table without an value for the NAME column works fine. But if I try this with the updateable view it raises the ORA-01400 error.
    Why does this error occure and why isn't the default value 'unknown' for the column NAME used? How to aviod this?

    CREATE TABLE TB1 (
    NUM NUMBER NOT NULL,
    NAME VARCHAR2(25) DEFAULT 'unknown' NOT NULL);

    CREATE VIEW VIEW1 AS
    SELECT TB1.NAME, TB1.NUM FROM TB1;


    CREATE TRIGGER INSTEAD_OF_TRIGGER_TB1 INSTEAD OF
    INSERT ON VIEW1
    begin
    insert into TB1(num, name)
    values (:new.num, :new.name);
    end;
    /

    SQL> INSERT INTO TB1(NUM) VALUES(11);

    1 row created

    SQL> SELECT * FROM TB1;

    NUM NAME
    ---------- -------------------------
    11 unknown

    SQL> INSERT INTO VIEW1(NUM) VALUES(12);
    INSERT INTO VIEW1(NUM) VALUES(12)
    *
    ERROR in line 1:
    ORA-01400: cannot insert NULL into ("SCOTT"."TB1"."NAME")
    ORA-06512: in "SCOTT.INSTEAD_OF_TRIGGER_TB1", Zeile 2
    ORA-04088: error during execution of trigger 'SCOTT.INSTEAD_OF_TRIGGER_TB1'
    Last edited by doofkopp; 11-06-03 at 11:01.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: updateable view with default values

    A default is only used when NO value is specified for a column in an insert statement, not when a NULL is specified. Your INSTEAD OF trigger specifies a value for tb1.name, so if that value is NULL it will try to store a NULL.

    To overcome this you can use NVL in the trigger:

    insert into TB1(num, name)
    values (:new.num, NVL(:new.name,'unknown'));

    Alternatively, instead of using DEFAULT you can add a BEFORE INSERT trigger on TB1:

    CREATE TRIGGER tb1_bi BEFORE INSERT ON tb1
    FOR EACH ROW
    WHEN (new.name IS NULL)
    BEGIN
    :new.name := 'unknown';
    END;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •