Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69

    Question Unanswered: How to update a field using a trigger

    I want to make a trigger that will update a record in a Oracle 8.1.7.4.0
    table.
    So if if update field A on this (single) record, the same value must be copied over to field B.
    I tried and tried but did not exceed. Can anyone help?
    (just example code)

    Error messages I get:

    ORA-06512: at stringline string
    Cause: Backtrace message as the stack is unwound by unhandled exceptions.
    Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA.

    Thanks in advance

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Show your TRIGGER code.

    Something basic would look like

    Code:
    create or replace trigger table_a_bt
    before insert or update or delete
    on table_a
    for each row
    begin
      if updating('FIELD_A') then
        update table_b
        set field_a = :NEW.FIELD_A
        where pk = :NEW.PK;
      end if;
    end table_a_bt;

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Try the following

    Code:
    create or replace trigger table_a_bt
    before update of field_a
    on table_a
    for each row
    begin
        :new.field_b := :new.field_a;
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Doh! I read that as an INSERT into another table! My bad.

  5. #5
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    Hi Beistwh,

    I'd like to add a substr function to it:

    create or replace trigger table_a_bt
    before update of field_a
    on table_a
    for each row
    begin
    :new.field_b := substr(:new.field_a,1,1);
    end;

    This will cause an error.
    Tried to add a variable, same problem.
    Can you help me?

    Rgds,

    Wim

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    You have something else wrong. This works for me

    Code:
    create table table_a (field_a varchar2(10), field_b varchar2(1));
    
    create or replace trigger table_a_bt
    before update of field_a
    on table_a
    for each row
    begin
     :new.field_b := substr(:new.field_a,1,1); 
    end;
    
    insert into table_a (field_a) values ('Chuck');
    
    select * from table_a;
    
    FIELD_A    F
    ---------- -
    Chuck     
    
    1 row selected.
    
    update table_a 
    set field_a = 'Chucky';
    
    select * from table_a;
    
    
    FIELD_A    F
    ---------- -
    Chucky     C
    
    1 row selected.
    If it matters, we're on version 10.1.0.3. What's the error message you're getting?

  7. #7
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    The message is:
    Missing IN or OUT parameter at index:: 1

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Please, post SQL*Plus session which will show exactly what you are doing (including Oracle error code and message which is - believe or not - helpful information).

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    I googled "Missing IN or OUT parameter at index:: 1", and it looks like this is a java exception? Try the code as I have it in SQL*Plus, and see if it works for you, from there, first. --=cf

  10. #10
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    It has to do the way you add the trigger:
    If I amend (adding the substr) the SQL in the Oracle Enterprise Manager Console I got the error message. When I run the SQL script it works...
    The only thing is, that the output shows an INVALID state. In the database I see a VALID state.

    I'd like to thank you all!

    Best regards,

    Wim

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    I'm at a loss then. Maybe someone else has an answer? Maybe you should be using SQL*Plus (or TOAD, or ...) instead of Oracle Enterprise Manager Console?

    --=Chuck

  12. #12
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    Hi Chuck,

    Problem solved, don't worry.

    Rgds and many thanks again.

    Wim

Posting Permissions

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