Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106

    Unanswered: Can't insert into a table

    Hi,

    I am trying to insert into a table that has a trigger on insert after each row through a SQL script. The trigger is expecting a value to insert a row in a log table. I don't know how can I pass this value to let that line execute.

    Please help.

    Thanks
    Mo

  2. #2
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    What type of error are you getting?

    Maybe you could post the code in the trigger that is failing so we can look at it?

  3. #3
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106
    Originally posted by marist89
    What type of error are you getting?

    Maybe you could post the code in the trigger that is failing so we can look at it?

    Hi,

    This is the errors I am getting;

    ORA-01400: cannot insert NULL into ("SFTESTUSER"."AUDITLOG"."USERID")
    ORA-06512:
    at "SFTESTUSER.ENCP_ADDTOAUDITLOG", line 111
    ORA-06512: at
    "SFTESTUSER.T1IT_RESUNAVA", line 12
    ORA-04088: error during execution of trigger
    'SFTESTUSER.T1IT_RESUNAVA'

    and this is the trigger code,

    create or replace trigger T1IT_RESUNAVA
    after insert on resunava
    for each row
    declare
    -- local variables here
    s varchar2(300);
    --UserName varchar2(30);
    FActiveLog char(1);
    begin
    select ACTIVELOG into FActiveLog from DEFAULTS;

    if FActiveLog = '1' then
    --select os_utils.get_os_username into UserName from dual;
    s := T1F_UnavaStr('',:new.datefrom,:new.dateto,ENCF_MIL TODIS(:new.timefrom),ENCF_MILTODIS(:new.Timeto),:n ew.reasoncode,:new.reason);
    ENCP_ADDTOAUDITLOG('RUADD','Resource unavailability "'||s||'" for resource %RESKEY'||:NEW.KEY_RESOUR|| ' was added.',DateFuncs.GetUserID);
    end if;
    end T1IT_RESUNAVA;


    Hope this helps.
    Mo

  4. #4
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    Actually, I think the problem lies with the SFTESTUSER.AUDITLOG table. I believe the USERID column has a not null constraint. You can verify this by either doing a:
    DESCRIBE SFTESTUSER.AUDITLOG
    and looking at the output for a NOT NULL COLUMN

    For example:
    Code:
    SQL> desc xyz
     Name                    Null?    Type
     ----------------------- -------- ----------------
     X                       NOT NULL VARCHAR2(10)
     Y                                VARCHAR2(20)
     Z                       NOT NULL DATE
    In this case, my X and Z columns have NOT NULL constraints and my Y column does not.

    You can also verify this through user_tab_columns:

    Code:
    SQL> select column_name, nullable
      2  from user_tab_columns
      3  where table_name = 'XYZ'
      4  /
    
    COLUMN_NAME                    N
    ------------------------------ -
    X                              N
    Y                              Y
    Z                              N
    Check that out, that's probably your problem.

    If this were my application [...opinion deleted for fear of flames...]

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by marist89
    If this were my application [...opinion deleted for fear of flames...]
    I think it would be better to give your opinion (constructively) than to imply you have one but not say what it is!
    </FLAME>

  6. #6
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106
    It is not my application anyway. It is one of our vendors I am trying to learn it on the fly.

    Thanks for the help.
    Mo

Posting Permissions

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