Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2003
    Posts
    10

    Question Unanswered: MUTATING Table, how to resolve it?

    create or replace trigger transactionUpdate
    after insert
    on transaction
    for each row

    declare
    buy transaction.units%type;
    sell transaction.units%type;

    begin

    select sum(units) as "TOTAL UNIT" into buy
    from transaction
    where transaction.companyid = :new.CompanyId
    and transaction.shareholderid = :new.ShareholderId and transaction.type = 'B';

    select sum(units) as "TOTAL UNIT" into sell
    from transaction
    where transaction.companyid = :new.CompanyId
    and transaction.shareholderid = :new.ShareholderId and transaction.type = 'S';

    if (buy-sell) = 0 then
    update correspondenceinst
    set toDate = sysdate
    where shareholderId = :new.shareHolderId;

    update dividendinst
    set toDate = sysdate
    where shareholderId = :new.shareHolderId;
    end if;

    end transactionUpdate;
    /

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You can't "SELECT" from the table that is being triggered on.
    That's where the mutation is coming from ...

    Gregg

  3. #3
    Join Date
    Sep 2003
    Posts
    10
    so what should i do then?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The solution involves using statement level triggers and a package.
    See here for how:
    http://asktom.oracle.com/~tkyte/Mutate/index.html

  5. #5
    Join Date
    Sep 2003
    Posts
    10
    create or replace package pkgTransaction as

    type t_buy is table of transaction.units%type index by binary_integer;

    type t_sell is table of transaction.units%type index by binary_integer;

    v_buy t_buy;
    v_sell t_sell;
    v_NumEntries binary_integer :=0;

    end pkgTransaction;
    /

    create or replace trigger transactionUpdate
    after insert
    on transaction
    for each row

    declare
    buy transaction.units%type;
    sell transaction.units%type;

    begin

    pkgTransaction.v_NumEntries := pkgTransaction.v_NumEntries + 1;

    for v_loopIndex in 1..pkgTransaction.v_numEntries loop

    select sum(units) as "TOTAL UNIT" into buy
    from transaction
    where transaction.companyid = :new.CompanyId
    and transaction.shareholderid = :new.ShareholderId and transaction.type = 'B';

    select sum(units) as "TOTAL UNIT" into sell
    from transaction
    where transaction.companyid = :new.CompanyId
    and transaction.shareholderid = :new.ShareholderId and transaction.type = 'S';

    pkgTransaction.v_buy(pkgTransaction.v_NumEntries) := buy;
    pkgTransaction.v_sell(pkgTransaction.v_NumEntries) := sell;

    end loop;

    pkgTransaction.v_numEntries := 0;

    if (buy-sell) = 0 then
    update correspondenceinst
    set toDate = sysdate
    where correspondenceinst.shareholderId = :new.shareHolderId and correspondenceinst.companyid = :new.CompanyId ;

    update dividendinst
    set toDate = sysdate
    where dividendinst.shareholderId = :new.shareHolderId and dividendinst.companyid = :new.CompanyId ;
    end if;

    end transactionUpdate;
    /


    --> errors like this
    SQL> INSERT INTO TRANSACTION
    VALUES (11, 'S', 90, '12 SEP 2000', 3, 4, 4); 2
    INSERT INTO TRANSACTION
    *
    ERROR at line 1:
    ORA-04068: existing state of packages has been discarded
    ORA-04063: package body "S2013260.PKGTRANSACTION" has errors
    ORA-06508: PL/SQL: could not find program unit being called
    ORA-06512: at "S2013260.TRANSACTIONUPDATE", line 7
    ORA-04088: error during execution of trigger 'S2013260.TRANSACTIONUPDATE'

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Apparently your package body has errors? Try:

    SHOW ERROR PACKAGE BODY PKGTRANSACTION.

    Anyway, you are still attempting to select from transaction in your FOR EACH ROW trigger, which still won't work. Read Tom Kyte's article again, it involves using statement level triggers for some parts of the code.

  7. #7
    Join Date
    Sep 2003
    Posts
    10
    3/1 PLS-00103: Encountered the symbol "CREATE" when expecting one of
    the following:
    begin end function package pragma procedure subtype type use
    <an identifier> <a double-quoted delimited-identifier> form
    current cursor

    what does this mean anyway?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You seem to have the CREATE keyword somewhere in the body of your package. Show me this:

    select text
    from user_source
    where type='PACKAGE BODY'
    and name='PKGTRANSACTION'
    order by line;

  9. #9
    Join Date
    Sep 2003
    Posts
    10
    package body pkgTransaction as

    create or replace trigger shareTransaction
    after insert
    on transaction
    for each row

    declare
    buy transaction.units%type;
    sell transaction.units%type;


    TEXT
    --------------------------------------------------------------------------------
    begin

    select sum(units) as "TOTAL UNIT" into t_buy
    from transaction
    where transaction.companyid = :new.CompanyId
    and transaction.shareholderid = :new.ShareholderId and transaction.type = 'B'
    ;


    select sum(units) as "TOTAL UNIT" into t_sell
    from transaction

    TEXT
    --------------------------------------------------------------------------------
    where transaction.companyid = :new.CompanyId
    and transaction.shareholderid = :new.ShareholderId and transaction.type = 'S'
    ;


    end shareTransaction;

    create or replace trigger transactionUpdate
    after insert
    on transaction
    for each row

    TEXT
    --------------------------------------------------------------------------------

    begin


    if (t_buy-t_sell) = 0 then
    update correspondenceinst
    set toDate = sysdate
    where correspondenceinst.shareholderId = :new.shareHolderId and correspond
    enceinst.companyid = :new.CompanyId ;



    TEXT
    --------------------------------------------------------------------------------
    update dividendinst
    set toDate = sysdate
    where dividendinst.shareholderId = :new.shareHolderId and dividendinst.com
    panyid = :new.CompanyId ;

    end if;

    end transactionUpdate;

    end pkgTransaction;

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by kiddz7
    package body pkgTransaction as

    create or replace trigger shareTransaction
    ...
    You somehow have your trigger definition buried inside your package definition!

  11. #11
    Join Date
    Sep 2003
    Posts
    10
    create or replace package pkgTransaction as

    type t_units is table of transaction.units%type index by binary_integer;

    v_total transaction.units%type;

    v_units t_units;

    v_NumEntries binary_integer :=0;

    end pkgTransaction;
    /

    create or replace trigger rowTransaction
    after insert
    on transaction
    for each row

    begin

    pkgTransaction.v_NumEntries := pkgTransaction.v_NumEntries + 1;
    pkgTransaction.v_units(pkgTransaction.v_NumEntries ) := :new.units;

    end rowTransaction;
    /



    create or replace trigger transactionShare
    after insert
    on transaction
    for each row

    declare
    buy transaction.units%type;
    sell transaction.units%type;
    v_units transaction.units%type;

    begin


    for v_loopIndex in 1..pkgTransaction.v_numEntries loop

    v_units := pkgTransaction.v_units(v_loopIndex);

    select sum(v_units) as "TOTAL UNIT" into buy
    from transaction
    where transaction.companyid = :new.CompanyId
    and transaction.shareholderid = :new.ShareholderId and transaction.type = 'B';

    select sum(V_units) as "TOTAL UNIT" into sell
    from transaction
    where transaction.companyid = :new.CompanyId
    and transaction.shareholderid = :new.ShareholderId and transaction.type = 'S';

    pkgTransaction.v_total := (buy-sell);

    end loop;

    pkgTransaction.v_numEntries := 0;

    end transactionUpdate;
    /


    create or replace trigger transactionUpdate
    after insert
    on transaction
    for each row

    begin

    if pkgTransaction.v_total = 0 then
    update correspondenceinst
    set toDate = sysdate
    where correspondenceinst.shareholderId = :new.shareHolderId and correspondenceinst.companyid = :new.CompanyId ;

    update dividendinst
    set toDate = sysdate
    where dividendinst.shareholderId = :new.shareHolderId and dividendinst.companyid = :new.CompanyId ;
    end if;

    end transactionUpdate;
    /


    now the errors is

    INSERT INTO TRANSACTION
    VALUES (11, 'S', 90, '12 SEP 2000', 3, 4, 4);SQL> 2
    INSERT INTO TRANSACTION
    *
    ERROR at line 1:
    ORA-04098: trigger 'S2013260.SHARETRANSACTION' is invalid and failed
    re-validation

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use SHOW ERRORS TRIGGER <triggername>

  13. #13
    Join Date
    Sep 2003
    Posts
    10
    8/1 PLS-00103: Encountered the symbol "BEGIN" when expecting one of
    the following:
    constant exception <an identifier>
    <a double-quoted delimited-identifier> table LONG_ double ref
    char time timestamp interval date binary national character
    nchar

    29/1 PLS-00103: Encountered the symbol "CREATE" when expecting one of
    the following:
    begin function package pragma procedure form

  14. #14
    Join Date
    Sep 2003
    Posts
    10
    ah i give up , i dont understand at all

Posting Permissions

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