12 Replies Latest reply: Nov 14, 2012 9:03 AM by padders RSS

    subquery in IF statement in trigger, without using foreign keys

    968875
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                Agree although I can't help wondering what the "_inline_sql_in_plsql" parameter does :-D