Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Wrong triggers are running

    I am baffled. One of our developers is dropping all triggers for a table, then running a DML statement. However, another table's trigger is voicing an error when the DML is executed
    Code:
    SQL> alter table claims.cc_claim disable all triggers;
    
    Table altered.
    
    SQL> delete
      2  from claims.cc_claim
      3  where filenr = 'BAM4711';
    from claims.cc_claim
                *
    ERROR at line 2:
    ORA-20190: List Undefined in ps_list.assert_existence...
    ORA-06512: at "CLAIMS.CA_RECONSIDERATION_AST", line 2768
    ORA-04088: error during execution of trigger 'CLAIMS.CA_RECONSIDERATION_AST'
    I am just ... lost ... on this one.
    ---=Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    As SYS run -
    select owner, trigger_name, trigger_type from dba_triggers where table_owner = 'CLAIMS' and table_name = 'CC_CLAIM';
    & post results back here via cut & paste.

    Which username is running the ALTER TABLE?
    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
    Dec 2003
    Posts
    1,074
    Code:
    SQL> select owner, trigger_name, trigger_type, status 
      2  from dba_triggers 
      3  where table_owner = 'CLAIMS' and table_name = 'CC_CLAIM';
    
    OWNER                          TRIGGER_NAME                   TRIGGER_TYPE
    ------------------------------ ------------------------------ ----------------
    STATUS
    --------
    CLAIMS                         CC_CLAIM_AST                   AFTER STATEMENT
    DISABLED
    
    CLAIMS                         CC_CLAIM_BST                   BEFORE STATEMENT
    DISABLED
    
    CLAIMS                         CC_CLAIM_BT                    BEFORE EACH ROW
    DISABLED
    The developer is running the account, and is as much a member of the DBA role as is possible, without being a member ('CREATE ANY TABLE, DELETE ANY TABLE'). As a member of the DBA role, though, I get the same error. Same if I log in as SYSDBA.

    I'm not sure what all goes into a DELETE, but I ran a trace on the DELETE, and the following was executed, which I thought was strange (this trigger is on the CC_CLM_DISPUTE_AGRMNT table)
    Code:
    SELECT COUNT(*)  
    FROM ALL_TRIGGERS  
    WHERE TRIGGER_NAME = :B1 
    (where :B1 = 'CC_CLM_DISPUTE_AGRMNT_AST')
    This stmt is executed several times, actually, with different triggers mentioned.


    And, there are also references to a completely different table.
    Code:
    select /*+ all_rows */ count(1)  
    2. from "CLAIMS"."CA_ACTION"  
    3. where "FILENR" = :1 
    (where :1 = 'BAM4711')
    ---=cf
    Last edited by chuck_forbes; 06-04-07 at 18:39.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    OS Name & version?
    Oracle Version to 4 decimal places?
    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.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Oracle 10.1.0.3.0
    RedHat Enterprise Linux AS3 version 8

    ---=cf

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I did a quick scan of Metalink, but nothing jumped out at me.
    I suspect a bug, but too early to know for sure.
    If you can, you might try the Microsoft approach & bounce the DB.
    It might be worthwhile to empty the recycyle bin.
    Good Luck, I've just been sucked into a black hole & need to deal with Chicken Little.
    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.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Is ORA-20190 a local "user defined" exception?
    Might it be misreporting reality?
    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.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Looking at 'CLAIMS.CA_RECONSIDERATION_AST' might help.

    It could also be foreign keys which are causing other tables and triggers to be looked at.

    Alan

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The current behavior of the RAISE_APPLICATION_ERROR package,
    which one can use in database triggers, procedures, and functions
    is to return not only the application error, but also a full error stack
    indicating where the application error was raised. Therefore, when you
    use RAISE_APPLICATION_ERROR inside a database trigger or stored
    procedure, your own error number and message are returned as well
    as ORA-06512. If the RAISE_APPLICATION_ERROR occurs in a database
    trigger, you also get ORA-04088.
    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.

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    CREATE OR REPLACE TRIGGER CLAIMS.CA_RECONSIDERATION_AST
    after
    INSERT or UPDATE or DELETE
    on CA_RECONSIDERATION
    declare
    ...
    I just dropped the triggers on CA_RECONSIDERATION, ran the DELETE, which triggered a similar error referencing another trigger. Recreated the CA_RECONSIDERATION triggers, and now the same error is back.

    ---=Chuck

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    OOOooo, I bet that's it, a DELETE CASCADE RI constraint somewhere.
    --=cf

Posting Permissions

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