Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Posts
    4

    Unanswered: Subquery in Trigger fails with ORA-02251 - how to phrase it correctly?

    Hi!

    I'm new to triggers and the when-conditions therein - so maybe my question may seem silly to experienced trigger-creators, but:

    After an insert or update in table A I want to insert a row by trigger into table B. Before inserting into B I want to make sure that the row doesn't exist there already.

    The create statement is as follows:

    Code:
    CREATE TRIGGER PDF_PERMISSION_ENTRY
        AFTER INSERT OR UPDATE ON CA_REPORTSTATICDESIGNCLOB
        FOR EACH ROW
        WHEN (NEW.SEQUENCENR = 0 AND 
                  NEW.OBJID NOT IN (SELECT DISTINCT OBJID FROM  
                  ca_reportstaticparameter where PARAMTYPE = 3 AND 
                  PARAMKEY = 'PERMISSIONS')
        BEGIN
            INSERT INTO CA_REPORTSTATICPARAMETER 
            VALUES(:NEW.OBJID, 3, 'PERMISSIONS', 3900);
        END;
    It did work perfectly when not having the subquery in the WHEN-clause but only the checking wether NEW.SEQUENCENR equals zero.

    I looked it up in the SQL-Reference and I thought that "NOT IN" in a WHEN-condition in combination with an SQL-statement is explicitly allowed - but obviously I'm wrong since I'm getting an ORA-02251 "Unterabfrage nicht zulässig".

    Any hints regarding the correct syntax? Thanks a lot in advance.
    Last edited by big_matze; 02-24-09 at 10:08. Reason: Clarification

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, that syntax isn't allowed in PL/SQL. You could do this:
    Code:
    CREATE TRIGGER PDF_PERMISSION_ENTRY
        AFTER INSERT OR UPDATE ON CA_REPORTSTATICDESIGNCLOB
        FOR EACH ROW
        WHEN (NEW.SEQUENCENR = 0 )
        BEGIN
            INSERT INTO CA_REPORTSTATICPARAMETER 
            SELECT :NEW.OBJID, 3, 'PERMISSIONS', 3900
            FROM DUAL
            WHERE NOT EXISTS
            (SELECT NULL FROM ca_reportstaticparameter 
             WHERE paramtype = 3 
             AND paramkey = 'PERMISSIONS'
             AND objid = :NEW.objid
            );
        END;
    or
    Code:
    CREATE TRIGGER PDF_PERMISSION_ENTRY
        AFTER INSERT OR UPDATE ON CA_REPORTSTATICDESIGNCLOB
        FOR EACH ROW
        WHEN (NEW.SEQUENCENR = 0)
        BEGIN
            INSERT INTO CA_REPORTSTATICPARAMETER 
            VALUES(:NEW.OBJID, 3, 'PERMISSIONS', 3900);
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN NULL; -- Ignore the failed duplicate insert
        END;

  3. #3
    Join Date
    Dec 2008
    Posts
    4

    Change of the trigger

    Hello Tony!

    Thanks a lot - that was tremendously helpful, both suggestions are fine.

    The second one is a great idea but doesn't work in my situation - I've found that the application has no unique index on the table in question.

    The first one works just fine.

    It's a viable workaround for me as a consultant until the application developers integrate a user-friendly, dialogue-driven management option for the pdf-permission values. Now the users can print their pdf-reports and are content for the time being (hopefully!).

    Again - thank you!

    Mathew

  4. #4
    Join Date
    Feb 2009
    Posts
    62
    Out of curiosity, what area or field are you a consultant in?

Posting Permissions

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