Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    12

    Question Unanswered: An trigger problem

    I have next trivial trigger wrote for informix (ids9):

    CREATE TABLE gartprov (
    codart char(16) not null,
    varlog char(6),
    priori smallint not null
    );

    CREATE PROCEDURE sdm_raise_msg(
    p_isam integer,
    p_strmsg char(120)
    )
    RAISE EXCEPTION -746, 0, p_strmsg
    ;
    END PROCEDURE;


    CREATE TRIGGER gartprov_ins
    INSERT ON gartprov
    REFERENCING NEW AS nxt
    FOR EACH ROW

    WHEN ((
    SELECT COUNT(*)
    FROM gartprov
    WHERE codart = nxt.codart
    AND varlog = nxt.varlog
    AND priori = 99
    AND nxt.priori = 99
    ) > 1) (
    EXECUTE PROCEDURE sdm_raise_msg(0, 'PRIORIDAD 99 YA ASIGNADA PARA ESTE ARTICULO')
    );

    If I rewrite the trigger above, now in Oracle10g syntax, I get next two triggers (avoiding mutating table error):

    CREATE TABLE gartprov (
    codart char(16) not null,
    varlog char(6),
    priori number(5) not null
    );

    CREATE GLOBAL TEMPORARY TABLE tmp_mutating (
    p_rowid char(18)
    ) ON COMMIT PRESERVE ROWS;


    CREATE OR REPLACE PROCEDURE sdm_raise_msg(
    p_isam IN integer,
    p_strmsg IN varchar
    )
    AS

    -- ================================================== ===================
    -- Debug
    -- To activate debug mesages, execute command line $ set serveroutput on
    -- ================================================== ===================

    BEGIN
    RAISE_APPLICATION_ERROR (-20000, p_strmsg );
    END;
    .
    run

    CREATE OR REPLACE TRIGGER gartprov_ins_pre
    AFTER INSERT ON gartprov
    REFERENCING NEW AS nxt
    FOR EACH ROW

    BEGIN

    INSERT INTO tmp_mutating VALUES (:nxt.rowid);


    END gartprov_ins_pre;
    .
    run

    CREATE OR REPLACE TRIGGER gartprov_ins
    AFTER INSERT ON gartprov
    REFERENCING NEW AS nxt

    DECLARE
    N65550 integer;

    BEGIN
    -- Variable ID: N65550

    SELECT COUNT(*)
    INTO N65550
    FROM gartprov
    WHERE codart = (SELECT codart FROM gartprov a, tmp_mutating b WHERE a.rowid = b.p_rowid)
    AND varlog = (SELECT varlog FROM gartprov a, tmp_mutating b WHERE a.rowid = b.p_rowid)
    AND priori = 99
    AND (SELECT priori FROM gartprov a, tmp_mutating b WHERE a.rowid = b.p_rowid) = 99;


    IF (N65550 > 1) THEN
    sdm_raise_msg(0, 'PRIORIDAD 99 YA ASIGNADA PARA ESTE ARTICULO');

    END IF;

    DELETE FROM tmp_mutating;

    END gartprov_ins;
    .
    run


    The question is: is this the best way to convert this trigger the IDS9(informix) trigger to Oracle10g trigger?.

    This solutions is not good enough for us because our erp-solution makes an intensive use of triggers. I can give you a example.
    Whe have procedures called by triggers of different tables. To avoid the mutating table problem requires
    the same temporay table in both cases.

    Oracle will resolved the mutating table problem in next versions?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The second trigger (gartprov_ins) could be rewritten in such a way, without impact on its functionality (unless I'm wrong about it):
    Code:
    CREATE OR REPLACE TRIGGER gartprov_ins
       AFTER INSERT
       ON gartprov
    DECLARE
       n65550   INTEGER;
    BEGIN
       SELECT COUNT (*)
         INTO n65550
         FROM gartprov g, tmp_mutating b
        WHERE g.ROWID = b.p_rowid 
          AND g.priori = 99;
    
       IF (n65550 > 1)
       THEN
          sdm_raise_msg (0, 'PRIORIDAD 99 YA ASIGNADA PARA ESTE ARTICULO');
       END IF;
    
       DELETE FROM tmp_mutating;
    END gartprov_ins;
    To activate debug messages, you don't need to SET SERVEROUTPUT ON on the SQL*Plus command line; it can be done in the procedure using the DBMS_OUTPUT.ENABLE (size); (where "size" is size of the output buffer, up to 1.000.000).

    Will Oracle fix the mutating table error, well, I wouldn't know, but - it seems that it makes sense anyway. How can you use table contents when it is changing at the moment your statement gets executed?

  3. #3
    Join Date
    Mar 2006
    Posts
    12

    An trigger problem

    I can do it in informix without any problem and it's perfectly consistent.
    I can see all commited data, and data is beeing altered in my session.
    For that we think it's perfecly prosible unless it's a feature not resolved-supported in oracle.



    Quote Originally Posted by Littlefoot
    The second trigger (gartprov_ins) could be rewritten in such a way, without impact on its functionality (unless I'm wrong about it):
    Code:
    CREATE OR REPLACE TRIGGER gartprov_ins
       AFTER INSERT
       ON gartprov
    DECLARE
       n65550   INTEGER;
    BEGIN
       SELECT COUNT (*)
         INTO n65550
         FROM gartprov g, tmp_mutating b
        WHERE g.ROWID = b.p_rowid 
          AND g.priori = 99;
    
       IF (n65550 > 1)
       THEN
          sdm_raise_msg (0, 'PRIORIDAD 99 YA ASIGNADA PARA ESTE ARTICULO');
       END IF;
    
       DELETE FROM tmp_mutating;
    END gartprov_ins;
    To activate debug messages, you don't need to SET SERVEROUTPUT ON on the SQL*Plus command line; it can be done in the procedure using the DBMS_OUTPUT.ENABLE (size); (where "size" is size of the output buffer, up to 1.000.000).

    Will Oracle fix the mutating table error, well, I wouldn't know, but - it seems that it makes sense anyway. How can you use table contents when it is changing at the moment your statement gets executed?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Fine, but this is Oracle and not Informix; both are RDBMS's, but not exact copies of each other. Both, more or less, follow standards. Deal with the same problem differently. You can't force Oracle to be Informix; if you are not satisfied with possibilities and solutions Oracle provides, perhaps you should consider staying on Informix (or even choose another database, there are plenty of them on the market).

  5. #5
    Join Date
    Mar 2006
    Posts
    12

    Thumbs up A trigger problema

    Thanks for the answer.


    Quote Originally Posted by Littlefoot
    Fine, but this is Oracle and not Informix; both are RDBMS's, but not exact copies of each other. Both, more or less, follow standards. Deal with the same problem differently. You can't force Oracle to be Informix; if you are not satisfied with possibilities and solutions Oracle provides, perhaps you should consider staying on Informix (or even choose another database, there are plenty of them on the market).

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    What are you trying to do with the Informix triggers, a casual glance at the code suggests it is to prevent duplicates going in? If this is true you might be able to enforce this using an unique index or a PK. Or do you have more complex triggers which do different things?

    Alan

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by Littlefoot
    To activate debug messages, you don't need to SET SERVEROUTPUT ON on the SQL*Plus command line; it can be done in the procedure using the DBMS_OUTPUT.ENABLE (size); (where "size" is size of the output buffer, up to 1.000.000).
    btw, true you can call DBMS_OUTPUT.ENABLE(size) from within a procedure, but unless I'm missing something it only achieves anything useful if there was an earlier call to DBMS_OUTPUT.DISABLE, or a later call to DBMS_OUTPUT.GET_LINE/GET_LINES.

    Code:
    SQL> set serveroutput off
    
    SQL> BEGIN
      2     DBMS_OUTPUT.ENABLE(1000000);
      3     DBMS_OUTPUT.PUT_LINE('Good morning and welcome');
      4  END;
      5  /
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Code:
    SQL> var outputline varchar2(255)
    
    SQL> DECLARE
      2     v_dbmsoutput_status INTEGER;
      3  BEGIN
      4     DBMS_OUTPUT.ENABLE(1000000);
      5     DBMS_OUTPUT.PUT_LINE('Good morning and welcome');
      6     DBMS_OUTPUT.GET_LINE(:outputline, v_dbmsoutput_status);
      7  END;
      8  /
    
    PL/SQL procedure successfully completed.
    
    
    OUTPUTLINE
    ---------------------------------------------------------------------------
    Good morning and welcome
    
    SQL> show serverout 
    serveroutput OFF
    Also in 10g the size limit is removed.

Posting Permissions

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