Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: trigger help

  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: trigger help

    These are the triggers that I am using. The issue is that once a usr makes several changes the transactions are never commited to the kb_stagetransaction table. Is there some way the commit this in the trigger???


    create or replace package pkg is
    type stagekey_tab_type is table of kb_errortransaction.stagekey%type index by binary_integer;
    stagekey_tab stagekey_tab_type;
    end;
    /

    CREATE OR REPLACE TRIGGER ERROR_CORRECTION
    after update on kb_errortransaction
    REFERENCING NEW AS newRow
    FOR EACH ROW
    begin
    -- Do everything except the problem delete
    ...
    -- Do this instead of the delete
    pkg.stagekey_tab( stagekey_tab.count+1 ) := :newrow.stagekey;
    end;
    /

    CREATE OR REPLACE TRIGGER ERROR_CORRECTION2
    after update on kb_errortransaction
    REFERENCING NEW AS newRow
    -- Note: no FOR EACH ROW here!
    begin
    -- Delete all the rows
    forall i in 1..pkg.stagekey_tab.count
    delete kb_errortransaction where stagekey= pkg.stagekey_tab(i);
    -- Clear the table for next time
    pkg.stagekey_tab.delete;
    end;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trigger help

    Why would you want to commit within the trigger? Any changes made by the trigger will be committed or rolled back along with the rest of the transaction that fired the trigger.

    An exception would be where you wanted to log FAILED actions in a trigger, in which case you could use an autonomous transaction:
    PHP Code:
    create or replace trigger trg before insert on tab
    for each row
    declare
      
    pragma autonomous_transaction;
    begin
      
    if user 'ANDREWST' then
        insert into error_log 
    (textvalues ('ANDREWST tried to insert a record!');
        
    commit;
        
    raise_application_error (-20000'You do not have sufficient privilege');
      
    end if;
    end
    Here the COMMIT commits only the insert into error_log, not the insert into tab.

    In all other cases, trigger actions should be considered part of the triggering transaction, otherwise you will corrupt the database.

  3. #3
    Join Date
    Jan 2004
    Posts
    5

    Re: trigger help

    that sounds logical but that is not what is happening. The transactions are edited in the error table and the triggers should insert them into the stage table. It seems that the transactions (sometimes) do not make it to the stage table. I made the changes via TOAD and when co-worker looked for the transactions in stage he could not see them. The transactions deleted from error but never went to stage table.



    Originally posted by andrewst
    Why would you want to commit within the trigger? Any changes made by the trigger will be committed or rolled back along with the rest of the transaction that fired the trigger.

    An exception would be where you wanted to log FAILED actions in a trigger, in which case you could use an autonomous transaction:
    PHP Code:
    create or replace trigger trg before insert on tab
    for each row
    declare
      
    pragma autonomous_transaction;
    begin
      
    if user 'ANDREWST' then
        insert into error_log 
    (textvalues ('ANDREWST tried to insert a record!');
        
    commit;
        
    raise_application_error (-20000'You do not have sufficient privilege');
      
    end if;
    end
    Here the COMMIT commits only the insert into error_log, not the insert into tab.

    In all other cases, trigger actions should be considered part of the triggering transaction, otherwise you will corrupt the database.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trigger help

    I don't see any code that inserts them into the stage table in your triggers. Please post the whole trigger.

  5. #5
    Join Date
    Jan 2004
    Posts
    5
    INSERT TRIGGER:

    Declare
    v_userid varchar(10);
    begin
    /* When a user edits a field in the kb_errortransaction table this trigger is executed and
    inserts the edited record from kb_errortransaction to kb_stagetransaction. It also flags
    the record with a 1 in genericboolean5, inserts to date to genericdate5, nulls out the
    reasonid and genericdate4. */

    Select user into v_userid
    from Dual;
    Insert Into Kb_StageTransaction
    (STAGEKEY,
    BATCHNAME,
    ORDERID,
    LINENUMBER,
    SUBLINENUMBER,
    EVENTTYPE,
    ACCOUNTINGDATE,
    PRODUCTID,
    PRODUCTNAME,
    PRODUCTDESCRIPTION,
    VALUE,
    UNITOFMEASURE,
    NUMBEROFUNITS,
    UNITVALUE,
    ACTIONTYPE,
    COMPENSATIONDATE,
    PAYMENTTERMS,
    PONUMBER,
    CHANNEL,
    ALTERNATEORDERNUMBER,
    DATASOURCE,
    NATIVECURRENCY,
    NATIVECURRENCYAMOUNT,
    DISCOUNTPERCENT,
    DISCOUNTTYPE,
    BILLTOCUSTID,
    BILLTOCONTACT,
    BILLTOCOMPANY,
    BILLTOAREACODE,
    BILLTOPHONE,
    BILLTOFAX,
    BILLTOADDRESS1,
    BILLTOADDRESS2,
    BILLTOADDRESS3,
    BILLTOCITY,
    BILLTOSTATE,
    BILLTOCOUNTRY,
    BILLTOPOSTALCODE,
    BILLTOINDUSTRY,
    BILLTOGEOGRAPHY,
    SHIPTOCUSTID,
    SHIPTOCONTACT,
    SHIPTOCOMPANY,
    SHIPTOAREACODE,
    SHIPTOPHONE,
    SHIPTOFAX,
    SHIPTOADDRESS1,
    SHIPTOADDRESS2,
    SHIPTOADDRESS3,
    SHIPTOCITY,
    SHIPTOSTATE,
    SHIPTOCOUNTRY,
    SHIPTOPOSTALCODE,
    SHIPTOINDUSTRY,
    SHIPTOGEOGRAPHY,
    OTHERTOCUSTID,
    OTHERTOCONTACT,
    OTHERTOCOMPANY,
    OTHERTOAREACODE,
    OTHERTOPHONE,
    OTHERTOFAX,
    OTHERTOADDRESS1,
    OTHERTOADDRESS2,
    OTHERTOADDRESS3,
    OTHERTOCITY,
    OTHERTOSTATE,
    OTHERTOCOUNTRY,
    OTHERTOPOSTALCODE,
    OTHERTOINDUSTRY,
    OTHERTOGEOGRAPHY,
    REASONID,
    GENERICATTRIBUTE1,
    GENERICATTRIBUTE2,
    GENERICATTRIBUTE3,
    GENERICATTRIBUTE4,
    GENERICATTRIBUTE5,
    GENERICATTRIBUTE6,
    GENERICATTRIBUTE7,
    GENERICATTRIBUTE8,
    GENERICATTRIBUTE9,
    GENERICATTRIBUTE10,
    GENERICATTRIBUTE11,
    GENERICATTRIBUTE12,
    GENERICATTRIBUTE13,
    GENERICATTRIBUTE14,
    GENERICATTRIBUTE15,
    GENERICATTRIBUTE16,
    GENERICATTRIBUTE17,
    GENERICATTRIBUTE18,
    GENERICATTRIBUTE19,
    GENERICATTRIBUTE20,
    GENERICATTRIBUTE21,
    GENERICATTRIBUTE22,
    GENERICATTRIBUTE23,
    GENERICATTRIBUTE24,
    GENERICATTRIBUTE25,
    GENERICATTRIBUTE26,
    GENERICATTRIBUTE27,
    GENERICATTRIBUTE28,
    GENERICATTRIBUTE29,
    GENERICATTRIBUTE30,
    GENERICATTRIBUTE31,
    GENERICATTRIBUTE32,
    GENERICNUMBER1,
    GENERICNUMBER2,
    GENERICNUMBER3,
    GENERICNUMBER4,
    GENERICNUMBER5,
    GENERICNUMBER6,
    GENERICDATE1,
    GENERICDATE2,
    GENERICDATE3,
    GENERICDATE4,
    GENERICDATE5,
    GENERICDATE6,
    GENERICBOOLEAN1,
    GENERICBOOLEAN2,
    GENERICBOOLEAN3,
    GENERICBOOLEAN4,
    GENERICBOOLEAN5,
    GENERICBOOLEAN6,
    ECA1PARTICIPANTID,
    ECA1POSITIONNAME,
    ECA1POSITIONTYPEID,
    ECA2PARTICIPANTID,
    ECA2POSITIONNAME,
    ECA2POSITIONTYPEID,
    ECA3PARTICIPANTID,
    ECA3POSITIONNAME,
    ECA3POSITIONTYPEID,
    ECA4PARTICIPANTID,
    ECA4POSITIONNAME,
    ECA4POSITIONTYPEID,
    BUSINESSUNITNAME,
    STAGEPROCESSDATE,
    STAGEPROCESSFLAG,
    STAGEVALIDTRANSACTIONSEQ,
    STAGEBUSINESSUNITSEQ)

    VALUES (
    :newrow.STAGEKEY,
    :newrow.BATCHNAME,
    :newrow.ORDERID,
    :newrow.LINENUMBER,
    :newrow.SUBLINENUMBER,
    :newrow.EVENTTYPE,
    :newrow.ACCOUNTINGDATE,
    :newrow.PRODUCTID,
    :newrow.PRODUCTNAME,
    :newrow.PRODUCTDESCRIPTION,
    :newrow.VALUE,
    :newrow.UNITOFMEASURE,
    :newrow.NUMBEROFUNITS,
    :newrow.UNITVALUE,
    :newrow.ACTIONTYPE,
    :newrow.COMPENSATIONDATE,
    :newrow.PAYMENTTERMS,
    :newrow.PONUMBER,
    :newrow.CHANNEL,
    :newrow.ALTERNATEORDERNUMBER,
    :newrow.DATASOURCE,
    :newrow.NATIVECURRENCY,
    :newrow.NATIVECURRENCYAMOUNT,
    :newrow.DISCOUNTPERCENT,
    :newrow.DISCOUNTTYPE,
    :newrow.BILLTOCUSTID,
    :newrow.BILLTOCONTACT,
    :newrow.BILLTOCOMPANY,
    :newrow.BILLTOAREACODE,
    :newrow.BILLTOPHONE,
    :newrow.BILLTOFAX,
    :newrow.BILLTOADDRESS1,
    :newrow.BILLTOADDRESS2,
    :newrow.BILLTOADDRESS3,
    :newrow.BILLTOCITY,
    :newrow.BILLTOSTATE,
    :newrow.BILLTOCOUNTRY,
    :newrow.BILLTOPOSTALCODE,
    :newrow.BILLTOINDUSTRY,
    :newrow.BILLTOGEOGRAPHY,
    :newrow.SHIPTOCUSTID,
    :newrow.SHIPTOCONTACT,
    :newrow.SHIPTOCOMPANY,
    :newrow.SHIPTOAREACODE,
    :newrow.SHIPTOPHONE,
    :newrow.SHIPTOFAX,
    :newrow.SHIPTOADDRESS1,
    :newrow.SHIPTOADDRESS2,
    :newrow.SHIPTOADDRESS3,
    :newrow.SHIPTOCITY,
    :newrow.SHIPTOSTATE,
    :newrow.SHIPTOCOUNTRY,
    :newrow.SHIPTOPOSTALCODE,
    :newrow.SHIPTOINDUSTRY,
    :newrow.SHIPTOGEOGRAPHY,
    :newrow.OTHERTOCUSTID,
    :newrow.OTHERTOCONTACT,
    :newrow.OTHERTOCOMPANY,
    :newrow.OTHERTOAREACODE,
    :newrow.OTHERTOPHONE,
    :newrow.OTHERTOFAX,
    :newrow.OTHERTOADDRESS1,
    :newrow.OTHERTOADDRESS2,
    :newrow.OTHERTOADDRESS3,
    :newrow.OTHERTOCITY,
    :newrow.OTHERTOSTATE,
    :newrow.OTHERTOCOUNTRY,
    :newrow.OTHERTOPOSTALCODE,
    :newrow.OTHERTOINDUSTRY,
    :newrow.OTHERTOGEOGRAPHY,
    Null,
    :newrow.GENERICATTRIBUTE1,
    :newrow.GENERICATTRIBUTE2,
    :newrow.GENERICATTRIBUTE3,
    :newrow.GENERICATTRIBUTE4,
    :newrow.GENERICATTRIBUTE5,
    :newrow.GENERICATTRIBUTE6,
    :newrow.GENERICATTRIBUTE7,
    :newrow.GENERICATTRIBUTE8,
    :newrow.GENERICATTRIBUTE9,
    :newrow.GENERICATTRIBUTE10,
    :newrow.GENERICATTRIBUTE11,
    :newrow.GENERICATTRIBUTE12,
    :newrow.GENERICATTRIBUTE13,
    :newrow.GENERICATTRIBUTE14,
    :newrow.GENERICATTRIBUTE15,
    :newrow.GENERICATTRIBUTE16,
    :newrow.GENERICATTRIBUTE17,
    :newrow.GENERICATTRIBUTE18,
    :newrow.GENERICATTRIBUTE19,
    v_userid,
    :newrow.GENERICATTRIBUTE21,
    :newrow.GENERICATTRIBUTE22,
    :newrow.GENERICATTRIBUTE23,
    :newrow.GENERICATTRIBUTE24,
    :newrow.GENERICATTRIBUTE25,
    :newrow.GENERICATTRIBUTE26,
    :newrow.GENERICATTRIBUTE27,
    :newrow.GENERICATTRIBUTE28,
    :newrow.GENERICATTRIBUTE29,
    :newrow.GENERICATTRIBUTE30,
    :newrow.GENERICATTRIBUTE31,
    :newrow.GENERICATTRIBUTE32,
    :newrow.GENERICNUMBER1,
    :newrow.GENERICNUMBER2,
    :newrow.GENERICNUMBER3,
    :newrow.GENERICNUMBER4,
    :newrow.GENERICNUMBER5,
    :newrow.GENERICNUMBER6,
    :newrow.GENERICDATE1,
    :newrow.GENERICDATE2,
    :newrow.GENERICDATE3,
    null,
    trunc(sysdate),
    :newrow.GENERICDATE6,
    :newrow.GENERICBOOLEAN1,
    :newrow.GENERICBOOLEAN2,
    :newrow.GENERICBOOLEAN3,
    :newrow.GENERICBOOLEAN4,
    '1',
    :newrow.GENERICBOOLEAN6,
    :newrow.ECA1PARTICIPANTID,
    :newrow.ECA1POSITIONNAME,
    :newrow.ECA1POSITIONTYPEID,
    :newrow.ECA2PARTICIPANTID,
    :newrow.ECA2POSITIONNAME,
    :newrow.ECA2POSITIONTYPEID,
    :newrow.ECA3PARTICIPANTID,
    :newrow.ECA3POSITIONNAME,
    :newrow.ECA3POSITIONTYPEID,
    :newrow.ECA4PARTICIPANTID,
    :newrow.ECA4POSITIONNAME,
    :newrow.ECA4POSITIONTYPEID,
    :newrow.BUSINESSUNITNAME,
    :newrow.STAGEPROCESSDATE,
    :newrow.STAGEPROCESSFLAG,
    :newrow.STAGEVALIDTRANSACTIONSEQ,
    :newrow.STAGEBUSINESSUNITSEQ);

    Error_Correction_Pkg.stagekey_tab(Error_Correction _Pkg.stagekey_tab.count+1 ) := :newrow.stagekey;
    end;

    DELETE TRIGGER
    begin
    /* This trigger Deletes all the rows IN KB_ERRORTRANSACTION that have been edited */

    forall i in 1..Error_Correction_Pkg.stagekey_tab.count
    delete kb_errortransaction where stagekey= Error_Correction_Pkg.stagekey_tab(i);
    -- Clear the table for next time
    Error_Correction_Pkg.stagekey_tab.delete;
    end;

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I can't see any reason why that should happen. That insert will either be committed or rolled back same way as the delete. The problem must lie elsewhere, e.g. some other code that deletes the new records or something. I suppose a bug in Oracle is possible but extremely unlikely.

    By the way, you don't need to declare that variable v_userid; you can just use USER in the insert statement.

  7. #7
    Join Date
    Mar 2004
    Posts
    19

    Post Re: trigger help

    Sometimes, Trigger does not fire when DataTypes mis-match.
    You need to check the DataTypes of the table "Kb_StageTransaction"
    and table "kb_errortransaction".

    You can check this using a simple example :

    CREATE TABLE X_A
    (
    A1 VARCHAR2(10),
    A2 NUMBER(5),
    A3 VARCHAR2(30)
    );

    CREATE TABLE X_A_BAK
    (
    A1 VARCHAR2(10),
    A2 NUMBER(5),
    A3 VARCHAR2(30)
    );

    CREATE OR REPLACE TRIGGER TRG_X_A
    AFTER UPDATE ON X_A
    FOR EACH ROW
    BEGIN
    INSERT INTO X_A_BAK (A1, A2, A3)
    VALUES (:NEW.A1, :NEW.A1, USER);
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('SQLERRM : ' ||SQLERRM);
    END;

    INSERT INTO X_A VALUES('AB',1,'USER1');
    COMMIT;

    UPDATE X_A SET A1='A' WHERE A1='AB';
    COMMIT;

    In Table X_A_BAK.A2 (Which is a Number Column), I am trying to Put a Non-Numeric value. This will not populate the "X_A_BAK" table.

    Change the Trigger:

    CREATE OR REPLACE TRIGGER TRG_X_A
    AFTER UPDATE ON X_A
    FOR EACH ROW
    BEGIN
    INSERT INTO X_A_BAK (A1, A2, A3)
    VALUES (:NEW.A1, :NEW.A2, USER);
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('SQLERRM : ' ||SQLERRM);
    END;

    Now Update will populate the Record in "X_A_BAK".

    I think this will help you.
    -------------
    Pragati Swain

  8. #8
    Join Date
    Mar 2004
    Posts
    19
    Again, Your Triggers are not having exception handler.
    If you incorporate exception handler, this problem will not occur.
    In the exception block, You need to specify "ROLLBACK", if you want to rollback the whole transaction.
    Otherwise, it will commit in the table "X_A", even if the Insertion in "X_A_BAK" fails.

    Example:

    CREATE OR REPLACE TRIGGER TRG_X_A
    AFTER UPDATE ON X_A
    FOR EACH ROW
    BEGIN
    INSERT INTO X_A_BAK (A1, A2, A3)
    VALUES (:NEW.A1, :NEW.A1, USER);
    EXCEPTION
    WHEN OTHERS THEN
    BEGIN
    DBMS_OUTPUT.PUT_LINE('SQLERRM : ' ||SQLERRM);
    ROLLBACK;
    END;
    END;

    Hope this helps
    -------------
    Pragati Swain

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It would work better without any exception handler:
    PHP Code:
    CREATE OR REPLACE TRIGGER TRG_X_A
    AFTER UPDATE ON X_A
    FOR EACH ROW
    BEGIN
        INSERT INTO X_A_BAK 
    (A1A2A3)
        
    VALUES (:NEW.A1, :NEW.A1USER);
    END
    Now the triggering transaction will know it failed. In any case, you can't issue a ROLLBACK from a trigger.

  10. #10
    Join Date
    Mar 2004
    Posts
    19
    ROLLBACK WILL STOP THE UPDATE IN TABLE X_A.
    IF ROLLBAK IS NOT USED IT WILL UPDATE TABLE X_A.
    -------------
    Pragati Swain

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by pragatiswain
    ROLLBACK WILL STOP THE UPDATE IN TABLE X_A.
    IF ROLLBAK IS NOT USED IT WILL UPDATE TABLE X_A.
    Both statements are incorrect:

    1) ROLLBACK is illegal within a trigger (unless using autonomous transactions, which would be inappropriate here).

    2) If an exception is raised by the trigger then the triggering update of X_A will fail also.

    I suggest you try both ways yourself and see!

  12. #12
    Join Date
    Mar 2004
    Posts
    19
    IF ROLLBAK IS NOT USED IT WILL UPDATE TABLE X_A.

    This statement is absolutely Correct as I have tried it myself.
    -------------
    Pragati Swain

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by pragatiswain
    IF ROLLBAK IS NOT USED IT WILL UPDATE TABLE X_A.

    This statement is absolutely Correct as I have tried it myself.
    Stating something wrong in CAPITALS doesn't make it right!

    You have tried it yourself? What with - SQL Server? Because it is 100% invalid in Oracle!

    See:
    PHP Code:
    SQL*PlusRelease 9.2.0.1.0 Production on Tue Mar 30 22:43:39 2004

    Copyright 
    (c19822002Oracle Corporation.  All rights reserved.


    Connected to:
    Personal Oracle9i Release 9.2.0.1.0 Production
    With the Partitioning
    OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 
    Production

    SQL
    create table x_a a1 int );

    Table created.

    SQLcreate table x_a_bak a1 inta2 inta3 varchar2(30) );

    Table created.

    SQLCREATE OR REPLACE TRIGGER TRG_X_A
      2  AFTER UPDATE ON X_A
      3  
    FOR EACH ROW
      4  BEGIN
      5  INSERT INTO X_A_BAK 
    (A1A2A3)
      
    6  VALUES (:NEW.A1, :NEW.A1USER);
      
    7  EXCEPTION
      8  WHEN OTHERS THEN
      9  BEGIN
     10  DBMS_OUTPUT
    .PUT_LINE('SQLERRM : ' ||SQLERRM);
     
    11  ROLLBACK;
     
    12  END;
     
    13  END;
     
    14  /

    Trigger created.

    SQLREM Add a check constraint so that an exception gets raise
    SQL
    REM in the trigger
    SQL
    alter table x_a_bak add constraint c check (a2 != a1);

    Table altered.

    SQLinsert into x_a values (1);

    1 row created.

    SQLupdate x_a set a1 2
      2  
    /
    update x_a set a1 2
           
    *
    ERROR at line 1:
    ORA-04092cannot ROLLBACK in a trigger
    ORA
    -06512at "TONY.TRG_X_A"line 8
    ORA
    -02290check constraint (TONY.Cviolated
    ORA
    -04088error during execution of trigger 'TONY.TRG_X_A' 
    That shows your ROLLBACK doesn't work. Now to prove it isn't necessary:
    PHP Code:
    SQLCREATE OR REPLACE TRIGGER TRG_X_A
      2  AFTER UPDATE ON X_A
      3  
    FOR EACH ROW
      4  BEGIN
      5      INSERT INTO X_A_BAK 
    (A1A2A3)
      
    6      VALUES (:NEW.A1, :NEW.A1USER);
      
    7  END;
      
    8  /

    Trigger created.

    SQLselect from x_a;

            
    A1
    ----------
             
    1

    SQL
    select from x_a_bak;

    no rows selected

    SQL
    update x_a set a1=2;
    update x_a set a1=2
           
    *
    ERROR at line 1:
    ORA-02290check constraint (TONY.Cviolated
    ORA
    -06512at "TONY.TRG_X_A"line 2
    ORA
    -04088error during execution of trigger 'TONY.TRG_X_A'


    SQLselect from x_a;

            
    A1
    ----------
             
    1

    SQL
    select from x_a_bak;

    no rows selected 
    As you can see, because the INSERT in the trigger failed, the triggering UPDATE didn't happen either.

    Now, please either agree that this is correct or show me your SQL Plus session where it works according to your rules!

  14. #14
    Join Date
    Mar 2004
    Posts
    19
    Yes you are right.
    I also meant that. That stops the partial Transaction.
    (Transaction means Either Update in X_A, Insert in X_A_BAK should happen together or fail together.)
    Otherwise, Update happens but not the Insert in the trigger.

    Please try this:
    ----------------------------------------------------------------------------------
    SQL*Plus: Release 9.2.0.1.0 - Production on Tue Mar 30 16:33:41 2004

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
    JServer Release 9.2.0.3.0 - Production

    SQL> CREATE OR REPLACE TRIGGER TRG_X_A
    2 AFTER UPDATE ON X_A
    3 FOR EACH ROW
    4 BEGIN
    5 INSERT INTO X_A_BAK (A1, A2, A3)
    6 VALUES (:NEW.A1, :NEW.A1, USER);
    7 EXCEPTION
    8 WHEN OTHERS THEN
    9 BEGIN
    10 DBMS_OUTPUT.PUT_LINE('SQLERRM : ' ||SQLERRM);
    11 END;
    12 END;
    13 /

    Trigger created.

    SQL> INSERT INTO X_A VALUES('AB',1,'USER1');

    1 row created.

    SQL> COMMIT;

    Commit complete.

    SQL> UPDATE X_A SET A1='A' WHERE A1='AB';

    1 row updated.

    SQL> COMMIT;

    Commit complete.

    SQL> SELECT * FROM X_A;

    A1 A2 A3
    ---------- ---------- ------------------------------
    A 1 USER1

    SQL> SELECT * FROM X_A_BAK;

    no rows selected

    SQL>
    ----------------------------------------------------------------------------------

    May be you'll understand what I want to say.
    -------------
    Pragati Swain

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, now you have an exception handler that sweeps the error under the carpet and allows the triggering UPDATE to succeed. But the solution is not to add a ROLLBACK statement, it is to let the exception propagate up to the triggering insert by simply not handling it! Adding a ROLLBACK statement only "worked" in the sense that since using ROLLBACK is not allowed, trying to do so raises an exception (but not the relevant exception!)

    If for some reason you must handle the exception (e.g. to log it into a table), then you should re-raise the exception via a RAISE statement.

    Also, using DBMS_OUTPUT.PUT_LINE to communicate errors to the user is useless, as it only works in SQL Plus with SET SERVEROUTPUT ON. A client application (Forms, ASP, etc.) will never see that output. DBMS_OUTPUT is useful for debug messages, that's about all.

    To recap, this simple trigger is all that is required:
    PHP Code:
    CREATE OR REPLACE TRIGGER TRG_X_A
    AFTER UPDATE ON X_A
    FOR EACH ROW
    BEGIN
        INSERT INTO X_A_BAK 
    (A1A2A3)
        
    VALUES (:NEW.A1, :NEW.A1USER);
    END

Posting Permissions

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