This discussion is archived
12 Replies Latest reply: Nov 14, 2012 7:03 AM by padders RSS

subquery in IF statement in trigger, without using foreign keys

968875 Newbie
Currently Being Moderated
Hello,

I'm investigating ways of writing a subquery in an IF statement, which is placed inside a trigger.
I wanna write smth like IF (:new.jazz not in (select goldies from T where ... )) etc. I don't know whether the fact that the IF is in a trigger adds some additional restrictions. (Does it?)

So far I found the solution described here: SubQuery Comparison in If Statement which I find a bit tacky, I could have the 'cooleststarinthegalaxy' instead of 1 and seems you need to do extra light, but still extra lifting.

I also read about the possibility of using MERGE, which I'm currently researching.

Is there any other way?

Thanks

Edited by: BluShadow on 14-Nov-2012 13:37
fixed link

Edite by me: the question is how (if possible) to do this without a foreign key.

Edited by: questioningq12 on Nov 14, 2012 6:11 AM

Edited by: questioningq12 on Nov 14, 2012 6:13 AM
  • 1. Re: subquery in IF in trigger
    SomeoneElse Guru
    Currently Being Moderated
    I don't understand your post at all.

    What have you tried? What did or didn't work?
  • 2. Re: subquery in IF in trigger
    968875 Newbie
    Currently Being Moderated
    Say I have tables A(namea varchar(10)), B(nameb varchar(10)), and B contains tuples ('1stname','2ndname').

    I wrote a trigger before insertion, for each row, on table A. For a tuple t to be inserted, it should check whether t.namea is in the set of values nameb from B.
    E.g., INSERT INTO A VALUES('1stname') should work. But INSERT INTO A VALUES ('3rdname') should fail.

    Here is a slice of code, based on the solution I found on this forum:

    CREATE TRIGGER myTrigger
    BEFORE INSERT ON A
    FOR EACH ROW
    DECLARE
    X NUMBER(1):=0;
    BEGIN
    SELECT 1 INTO X FROM DUAL WHERE :NEW.namea NOT IN (SELECT nameb from B);
    IF X = 1
    THEN -- print msgs, raise exceptions etc
    END IF;
    END;

    This works fine for INSERT INTO A VALUES ('3rdname'), which doesn't get inserted into A. The trigger fails for INSERT INTO A VALUES('1stname'), with "no data found" error.

    I was wondering whether there exists an easier workaround. If not, how to cope with this error?

    Hope it's more clear now, Thanks
  • 3. Re: subquery in IF in trigger
    6363 Guru
    Currently Being Moderated
    questioningq12 wrote:

    I was wondering whether there exists an easier workaround.
    A foreign key constraint.

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/datainte.htm#CNCPT1649
  • 4. Re: subquery in IF in trigger
    968875 Newbie
    Currently Being Moderated
    The question is how to write a subquery in an IF statement (inside a trigger). This is just an example of implementing the referential integrity, it could be anything else.
  • 5. Re: subquery in IF in trigger
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    questioningq12 wrote:
    Say I have tables A(namea varchar(10)), B(nameb varchar(10)), and B contains tuples ('1stname','2ndname').

    I wrote a trigger before insertion, for each row, on table A. For a tuple t to be inserted, it should check whether t.namea is in the set of values nameb from B.
    E.g., INSERT INTO A VALUES('1stname') should work. But INSERT INTO A VALUES ('3rdname') should fail.
    You can use a foreign key constraint for that.

    If the tables already exist, and b.nameb is declared as UNIQUE (or PRIMARY KEY), then you can say:
    ALTER TABLE  a
        ADD CONSTRAINT     a_namea_fk
        FOREIGN KEY  (namea) 
        REFERENCES b (nameb)
    ;
    If you had a situation where you really needed to query a table in PL/SQL, and you weren't sure if the query would find anything, you could put the query in its own BEGIN ... EXCEPTION block, and test for NO_DATA_FOUND.
    If you're just checking to see if a row exists or not, you can always write a query that is guaranteed to return exactly 1 row, like this:
    ...
    BEGIN
        SELECT  COUNT (*)
        INTO    x 
        FROM    b
        WHERE   nameb = :NEW.namea 
        AND         ROWNUM  = 1;
    
        IF x = 0 
        THEN   
            ...        -- print msgs, raise exceptions etc
        END IF;
    END;
    Edited by: Frank Kulash on Nov 14, 2012 9:22 AM
    Added example
  • 6. Re: subquery in IF in trigger
    6363 Guru
    Currently Being Moderated
    questioningq12 wrote:
    The question is how to write a subquery in an IF statement (inside a trigger). This is just an example of implementing the referential integrity, it could be anything else.
    So when you said

    >
    I wrote a trigger before insertion, for each row, on table A. For a tuple t to be inserted, it should check whether t.namea is in the set of values nameb from B.
    >

    You actually didn't write a trigger to enforce referential integrity?

    That is good, because it doesn't work and the only thing it reliably achieves is to slow down DML.
  • 7. Re: subquery in IF in trigger
    968875 Newbie
    Currently Being Moderated
    I'm interested whether it can be done (and how), not in the performance of the query. I'm not convinced it doesn't work. How is ref integrity implemented in Oracle in the 1st place?

    Edited by: questioningq12 on Nov 14, 2012 6:26 AM
  • 8. Re: subquery in IF in trigger
    6363 Guru
    Currently Being Moderated
    questioningq12 wrote:

    I'm interested whether it can be done (and how),
    It can be done using a foreign key constraint.
    not in the performance of the query.
    That fact that it will slow down DML is just an additional feature.
    I'm not convinced it doesn't work.
    This does not change the fact that triggers cannot be used to enforce referential integrity.

    If you are still not convinced read and understand this

    http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

    >
    Triggers should be viewed with a skeptic’s eye. If the trigger is in place to enforce entity integrity, be very suspicious of it. Think about multiuser conditions. Think about what happens when two or three people operate on similar data at, or at about, the same time. Run all the combinations in your head or on the whiteboard. Play with your schema, using multiple sessions. See what happens when you have concurrent access.
    >
    How is ref integrity implemented in Oracle in the 1st place?
    Foreign key constraints.
  • 9. Re: subquery in IF in trigger
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    questioningq12 wrote:
    The question is how to write a subquery in an IF statement (inside a trigger). This is just an example of implementing the referential integrity, it could be anything else.
    Sorry, I don't understand. The example you gave does not include a subquery in an IF.
    Also, does the fact that whatever you want happens to occur in a trigger really matter? If not, it will be easier to write and test sample code that is not in a trigger.
    I'm interested whether it can be done (and how), not in the performance of the query. I'm not convinced it doesn't work.
    Yes, you can put a query inside an IF statement, either in a trigger or any other PL/SQL code. If you have trouble doing it, post your code, CREATE TABLE and INSERT statements for any tables needed (if you can't use, say, tables in the scott schema), the results you want, and the error message you get (if any).

    See the forum FAQ {message:id=9360002}
  • 10. Re: subquery in IF in trigger
    968875 Newbie
    Currently Being Moderated
    Still does not address the problem of writing a subquery in an If, inside a trigger, which is what the initial question was.
  • 11. Re: subquery in IF in trigger
    BluShadow Guru Moderator
    Currently Being Moderated
    questioningq12 wrote:
    Still does not address the problem of writing a subquery in an If, inside a trigger, which is what the initial question was.
    Ok, simple answer... you cannot write a subquery in an IF statement's condition, it's not part of the PL/SQL syntax.
  • 12. Re: subquery in IF in trigger
    padders Pro
    Currently Being Moderated
    Agree although I can't help wondering what the "_inline_sql_in_plsql" parameter does :-D

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points