Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    Unanswered: trigger with multiple "when" clause

    I have a table called tbl_products, and 3 sub-type of products identified by "V", "L", "C" in the product_type field in tbl_products. I would like to have a trigger identify which product_type it has, and put the data into the appropriate sub-type table. For example:

    CREATE TRIGGER insert_vod_product
    INSERT ON tbl_products
    REFERENCING NEW AS new
    FOR EACH ROW WHEN(new.product_type = "V")
    (
    INSERT INTO tbl_vods(product_id)
    VALUES (new.product_id)
    );

    it will insert a record in tbl_vods if product_type is "V"

    is there a way i can identify different product types within one trigger?

  2. #2
    Join Date
    Nov 2002
    Posts
    98

    Re: trigger with multiple "when" clause

    Answering my own question:

    CREATE TRIGGER insert_vod_product
    INSERT ON tbl_products
    REFERENCING NEW AS new
    FOR EACH ROW
    WHEN (new.product_type = "V")
    (
    INSERT INTO tbl_vods(product_id)
    VALUES (new.product_id)
    ),
    WHEN (new.product_type = "S")
    (
    INSERT INTO tbl_streams(product_id)
    VALUES (new.product_id)
    ),
    WHEN (new.product_type = "C")
    (
    INSERT INTO tbl_channels(product_id)
    VALUES (new.product_id)
    );


    tried many syntax to no avail; finally found out that i need to use comma to separate each "when" blocks......

  3. #3
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Or....
    You could create a stored procudure for the inserts and execute the procedure in the trigger...
    rws

  4. #4
    Join Date
    Nov 2002
    Posts
    98
    a continuing question relating to this thread:

    now my table has evolved so that tbl_product contains only the common attributes of the 3 sub-type products, and each sub-type has its own different attributes.

    what would be a good way to insert all the attributes with a trigger or a procedure?

    tbl_products has:
    product_id
    product_name

    tbl_videos has:
    product_id
    video_id
    video_category

    if a user wants to insert a video product, he would have the information for product_name and video_category (product_id and video_id would be automatically assigned as the previous example). However, what would be the best way to insert the data into the 2 corresponding tables and avoid any multi-threaded issue?

  5. #5
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Best way is to do the inserts into one transaction in your application.
    rws

  6. #6
    Join Date
    Nov 2002
    Posts
    98
    Yah it's true, but i haven't thought a way of implementing it...

    Thought about using views, but IDS doesn't allow insert on views with more than one table involved.

    So no matter how the application is written, the fact is i will need to do two inserts ( or one insert and one update) to my tables.

    so far the only solution is to introduce another unique attributes and use it as identification (since i can't really pass the current serial value back to the application... or can I?)

    Mark

Posting Permissions

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