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

Thread: Trigger

  1. #1
    Join Date
    Apr 2004
    Posts
    38

    Unanswered: Trigger

    After I created a table, then trigger is going to be created as well.
    I just want to know, could I make more than one trigger for a table?

    Thanks,
    Frenk

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, you can create any number of triggers on a table. However, it is usually advisable to combine triggers that fire at the same time - makes the code easier to manage.

  3. #3
    Join Date
    Apr 2004
    Posts
    38

    trigger

    Code:
    Create table INGREDIENT
    (
    ING_ID NUMBER(8),
    ING_NAME VARCHAR2(15),
    ING_QUAN NUMBER(3),
    ING_COST NUMBER(2,2)
    ING_GRAM NUMBER(3,2)
    CONSTRAINT INGREDIENT_ING_ID_PK PRIMARY KEY(ING_ID)
    )
    Create trigger I1
    After insert or update or delete on INGREDIENT
    reference new as newingr
    for each row
    when (newingr.ING_ID=10000001)
    begin
    Insert into PRODUCT@ora9edb1 
    values(:newingr.ING_ID, :newingr.ING_NAME, :newingr.ING_QUAN, :newingr.ING_COST, :newingr.ING_GRAM)
    End trigger;
    
    Create trigger I2
    After insert or update or delete on INGREDIENT
    reference new as newingr
    for each row
    when (newingr.ING_ID=10000002)
    begin
    Insert into PRODUCT@ora9edb1 
    values(:newingr.ING_ID, :newingr.ING_NAME, :newingr.ING_QUAN, :newingr.ING_COST, :newingr.ING_GRAM)
    End trigger;
    is it the correct way which I built more than one trigger in this table?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, the triggers are almost syntactically correct and will work once fixed, yes. But they don't make sense to me! Since ING_ID is the primary key, each trigger will only ever fire when one specific ingredient is added, updated or deleted.

    (Why would you insert into PRODUCT after deleting an ingredient?)

    You could combine them into a single trigger like this:
    Code:
    Create trigger I1_2
    After insert or update or delete on INGREDIENT
    referencing new as newingr
    for each row
    when (newingr.ING_ID IN (’10000001’,'10000002'))
    begin
      Insert into PRODUCT@ora9edb1 
      values  (:newingr.ING_ID, :newingr.ING_NAME, :newingr.ING_QUAN, :newingr.ING_COST, :newingr.ING_GRAM);
    End;
    But even then, why only fire for those 2 ingredients? Why not fire for every ingredient?

    As an aside, it is redundant and pointless to use the REFERENCING clause. Your trigger can be simplified to:

    Code:
    Create trigger I1_2
    After insert or update or delete on INGREDIENT
    for each row
    when (new.ING_ID IN (’10000001’,'10000002'))
    begin
      Insert into PRODUCT@ora9edb1 
      values  (:new.ING_ID, :new.ING_NAME, :new.ING_QUAN, :new.ING_COST, :new.ING_GRAM);
    End;
    Remove the WHEN clause and it will fire for all ingredients.

  5. #5
    Join Date
    Apr 2004
    Posts
    38

    trigger

    were 100001 and 1000002 come from same database?
    my idea is product table in ora9edb1 will store the details into tablespace 1 and tablespace 2 once it is updated.

    becasue 100001 is from tablespace 1 and 1000002 is from tablespace 2

    Regards,
    Frenk
    Last edited by fj8283888; 05-14-04 at 08:54.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sorry, but I don't understand what you mean by "tablespace 1" and "tablespace 2" here. Both your triggers fire on the same table (INGREDIENT), and both insert into the same table (PRODUCT@ora9edb1). In what sense is ING_ID 10000001 associated with a "tablespace 1"?

  7. #7
    Join Date
    Apr 2004
    Posts
    38

    Exclamation trigger

    Dear Tony:

    Sorry I did not make you understand, I have a database which is the main office, and branches (which will store in tablespace). once the main office updated the details of the table, the table in each branch should be updated as well.

    Regards,
    Frenk

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, well you might want to look at Oracle Replication rather than do it yourself. But anyway, it sounds like you want a trigger more like this:
    Code:
    CREATE OR REPLACE TRIGGER trg1
    AFTER INSERT OR UPDATE OR DELETE ON table1
    FOR EACH ROW
    BEGIN
      IF INSERTING THEN
        INSERT INTO table1@branch1 (x, y, z) VALUES (:new.x, :new.y, :new.z);
        INSERT INTO table1@branch2 (x, y, z) VALUES (:new.x, :new.y, :new.z);
        INSERT INTO table1@branch3 (x, y, z) VALUES (:new.x, :new.y, :new.z);
      ELSIF UPDATING THEN
        UPDATE table1@branch1 SET y=:new.y, z=:new.z WHERE x=:new.x;
        UPDATE table1@branch2 SET y=:new.y, z=:new.z WHERE x=:new.x;
        UPDATE table1@branch3 SET y=:new.y, z=:new.z WHERE x=:new.x;
      ELSIF DELETING THEN
        DELETE table1@branch1 WHERE x=:old.x;
        DELETE table1@branch2 WHERE x=:old.x;
        DELETE table1@branch3 WHERE x=:old.x;
      END IF;
    END;
    /
    But wouldn't it be easier if all branches shared the same table rather than having their own local copies?

  9. #9
    Join Date
    Apr 2004
    Posts
    38

    trigger

    Dear Tony:


    Thanks for your helps, is the branch1 database or tablesapce?

    when you put table1@branch1, branch1 is the database?

    can I do something like table1@tablespace ?

    Regards,
    Frenk

    Thank you so much Tony

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    "@branch1" means database branch1.

    It would be meaningless to try to specify a tablespace in a SQL statement: each table is located in one tablespace (assumigng the table is not partitioned), so if you insert into a particular table there is only one tablespace to "choose" from!

    I'm not sure quite what you think a tablespace is, but I think you have a misconception about it. If so, read all about tablespaces here in the Concepts Guide.

  11. #11
    Join Date
    Apr 2004
    Posts
    38

    trigger

    Code:
    CREATE OR REPLACE TRIGGER trg1
    AFTER INSERT OR UPDATE OR DELETE ON table1
    FOR EACH ROW
    BEGIN
      IF INSERTING THEN
        INSERT INTO table1@branch1 (x, y, z) VALUES (:new.x, :new.y, :new.z);
        INSERT INTO table1@branch2 (x, y, z) VALUES (:new.x, :new.y, :new.z);
        INSERT INTO table1@branch3 (x, y, z) VALUES (:new.x, :new.y, :new.z);
      ELSIF UPDATING THEN
        UPDATE table1@branch1 SET y=:new.y, z=:new.z WHERE x=:new.x;
        UPDATE table1@branch2 SET y=:new.y, z=:new.z WHERE x=:new.x;
        UPDATE table1@branch3 SET y=:new.y, z=:new.z WHERE x=:new.x;
      ELSIF DELETING THEN
        DELETE table1@branch1 WHERE x=:old.x;
        DELETE table1@branch2 WHERE x=:old.x;
        DELETE table1@branch3 WHERE x=:old.x;
      END IF;
    END;
    The code you just gave me as above, is it putting one value into three tables?
    I make some tablespaces for branch one and branch two.
    In this case can I create a coding like this way,
    UPDATE table1 tablespace branch1 SET y=:new.y, z=:new.z WHERE x=:new.x;
    can variable new and old be used at the same time?
    How to create it? how about the elseif(the one you put is elsif, how is correct?)
    Thanks

    Regards,
    Frenk
    Last edited by fj8283888; 05-14-04 at 22:33.

  12. #12
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    else if is same is elsif

    You have confused with the syntax and also as tony said the concept of tablespace also not clear for you. You will have to make your concepts clear before you start something
    Thanks and Regards,

    Praveen Pulikunnu

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    ELSIF is correct syntax yes, and is different from ELSE IF. Compare the following examples, which are equivalent:
    Code:
    IF x=1 THEN
       ...
    ELSIF x=2 THEN
       ...
    ELSIF x=3 THEN
       ...
    ELSIF x=4 THEN
       ...
    ELSE
       ...
    END IF;
    Code:
    IF x=1 THEN
       ...
    ELSE
       IF x=2 THEN
          ...
       ELSE
          IF x=3 THEN
             ...
          ELSE
             IF x=4 THEN
                ...
            ELSE
               ...
            END IF;
          END IF;
      END IF;
    END IF;
    Your imaginary syntax "UPDATE table1 tablespace branch1 SET ..." is just that - imaginary, I'm afraid! You seem to be confusing tablespaces with databases, or possibly tablespaces with schemas, it's hard to be sure.

    You can do this (specify database):

    UPDATE table1@db1 SET ...

    or this (specify schema, i.e. table "owner"):

    UPDATE schema1.table1 SET ...

    or even a combination:

    UPDATE schema1.table1@db1 SET ...

    But the tablespace is not something you can specify in an UPDATE statement.

    Try reading the Concepts Guide using the link I gave you. If you are still confused after that, you would be better off posting a question asking how to go about achieving your requirements - tablespaces are not the solution.

  14. #14
    Join Date
    Apr 2004
    Posts
    38

    Trigger

    Could I say that tablespace and schemas is the same?

    Thanks,
    Frenk

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, you could not. A schema defines who owns the table, a tablespace defines where it is physically located (more or less).

    A schema may have tables in several tablespaces, and a tablespace may include tables from several schemas.

    Really, you should study the Concepts Guide via the link I gave earlier!

Posting Permissions

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