Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Unanswered: recordset in a trigger

    I am attempting to create an after insert trigger on a table that will generate values in another table.

    To generate these values, the trigger would need to do a select query on a table to get the appropriate list of values (depending on the value just entered into the table) then a record is created in the audit table for each of the values returned from the query combined with values that were used in the original update.

    to make that a little clearer a simplified version is:
    table1(number,name,position) where number is PK
    table2(idNum, number, amount) where idNum is PK and number is FK to table1
    table3(transNum, name, position, amount) where transNum is PK

    When a value is added to table2, I want a trigger to do the following:
    select name from table1 where number = new number entered in table2
    for each row in this recordset create a record in table3

    There will be multiple rows added to table3 and they are needed for historical purposes, the values in table1 and table2 will change over time but the connection at the time of this update is important so they are stored in a third table.

    How do I get a recordset inside of trigger? When I try to do the select statement it generates an error and the trigger is not made.

    Thanks!

    Steve

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How do I get a recordset inside of trigger?
    Why are you certain that a "recordset" is required to be patr of the solution?

    >When I try to do the select statement it generates an error and the trigger is not made.
    To get useful answers, it would be EXTREMELY helpful if you would post the actual SELECT and SPECIFIC error message!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Just like Anacedent told you, it is not easy to guess what went wrong with your code when you don't provide it.

    However, let me try ...
    You need an AFTER INSERT trigger on your "table2". In there you could declare a cursor (instead of a record set) to select data from "table1" and, afterwards, insert it into "table3". You can do it in the same loop (and, therefore, you don't need plsql tables or something like that).

    It would look like this, for example:
    Code:
    cursor c_tab1 is select ... from table1 where ...;
    begin
      for cr_tab1 in c_tab1 loop
        /* Do something with data cursor provided. */
        insert into table3
          (column1, ..., columnx)
          values
          (calculated_value1, ..., calculated_valuex);
      end loop;
    end;
    But, if you don't have to calculate something with the data cursor privided, I guess you don't need cursor at all:
    Code:
    begin
      insert into table3
        (column1, ..., columnx)
        (select value1, ..., valuex
         from table1
         where your_condition_here
        );
    end;
    And the error you got ... I'd say you received TOO-MANY-ROWS as your SELECT statement returned a few values into a declared variable(s).

    Do post a little bit more about your problem, and someone might help you better.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    FYI - If you are selecting from the same table the trigger is
    based on (firing from) then you will get an error based on a mutating table.
    PHP Code:
    SQLcreate table test (a varchar2(50));

    Table created.

    SQLinsert into test values ('2');

    SQLCREATE OR REPLACE TRIGGER TEST_TRG
        BEFORE INSERT 
    OR UPDATE ON TEST
        
    FOR EACH ROW
    DECLARE
    BEGIN
      
    for v_data in (select from test)
       
    loop
        null
    ;
       
    end loop;
    END;


    Trigger created.

    SQLupdate test set a '3' where a '2';
    update test set a '3' where a '2'
           
    *
    ERROR at line 1:
    ORA-04091table TEST.TEST is mutatingtrigger/function may not see it
    ORA
    -06512at "TEST.TEST_TRG"line 5
    ORA
    -04088error during execution of trigger 'TEST.TEST_TRG' 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Sep 2004
    Posts
    16
    "table1(number,name,position) where number is PK
    table2(idNum, number, amount) where idNum is PK and number is FK to table1
    table3(transNum, name, position, amount) where transNum is PK

    When a value is added to table2, I want a trigger to do the following:
    select name from table1 where number = new number entered in table2
    for each row in this recordset create a record in table3"

    In table1, your PK is number.
    Then, if you execute
    "select name from table1 where number = new number entered in table2",
    shouldnt that return only 1 unique row, number being the primary key?
    You wouldnt have any recordsets then.

    -Vinita

  6. #6
    Join Date
    Sep 2004
    Posts
    16
    However, assuming that you want to get multiple records for a non-unique column from table1, you can do the following:

    create or replace trigger table2BI
    before insert on table2
    for each row
    declare
    begin
    for i in (select * from table1 where number = :new.number) loop
    insert into table3(transNum, name, position, amount)
    values(<your transnum value>,i.name,i.position,:new.amount);
    end loop;
    end;

    -Vinita
    Last edited by vinitasinha; 11-09-04 at 03:28.

Posting Permissions

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