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?

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......

Or....
You could create a stored procudure for the inserts and execute the procedure in the trigger...

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?

Best way is to do the inserts into one transaction in your application.

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