Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    21

    Unanswered: Trigger and update

    Hi every one . I have two table with name product and sales . my product table has field pro_qty . this field show the number of product
    and table sales that use for register product name and product id .
    sales has pro_id that is foreign key

    i want create a trigger after insert in tables sales for decrement pro_qty in table product .

    create trigger dec_num
    after insert on sales
    For each row mode db2sql
    UPDATE product p
    SET pro_qty = pro_qty - 1
    WHERE EXISTS
    (SELECT 0
    FROM sales s
    WHERE s.pro_id = p.pro_id
    );
    i created this this trigger but after trigger run all my pro_qty decrees by one

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You already posted some threads about same problem.

    http://www.dbforums.com/db2/1659611-update.html

    http://www.dbforums.com/db2/1659602-trigger.html

    http://www.dbforums.com/db2/1659599-trigger-error.html

    I hope you to continue former thread by replying to my question.

  3. #3
    Join Date
    Aug 2010
    Posts
    21

    Hi

    i answered to your question

  4. #4
    Join Date
    Aug 2010
    Posts
    21
    and now completely show my question

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think you didn't answer to my question in thread http://www.dbforums.com/db2/1659611-update.html

    insert into sales values ('s2',1.34,'2010-8-1','m1','p1h2','h2');
    PRO_ID PRO_QTY
    ---------- -----------
    p1h1 8
    p1h2 9
    p1c1 10
    p1a1 10
    How to know(or relate) the row prod_id = p1h1 in product from the inserted row ('s2',1.34,'2010-8-1','m1','p1h2','h2') of sales?
    Last edited by tonkuma; 08-26-10 at 15:04.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have some other questions to your answer.

    create table product(
    pro_id char(10) not null primary key,
    pro_name varchar(20) check (pro_name in('airplane','helicopter','boat','car')),
    pro_model varchar(50),
    pro_color varchar(10),
    pro_type char(11) check (pro_type in ('electronic','gas')),
    pro_enginesize int check (pro_enginesize in (1,2,3,4)),
    pro_speed KM,
    pro_weight KG,
    pro_qty int check (pro_qty >1)
    );
    The DDL in your answer include check constraints.

    Q1) Two(for pro_name and pro_type) are too restrictive.
    Even if the actual list were longer than that, constant list for these columns seems not practical.
    Do you realy want to use such constraints?

    For example:
    If I designed the tables, I want to create product_name table and add foreign key constraint on product table referencing the product_name table.

    Q2) If pro_qty get to one, do you want to delete the row?
    Or do you want not to update the row, if "set pro_qty = pro_qty -1" made pro_qty to 1?
    Anyhow, I felt that the check constraint itself was not practical.
    Last edited by tonkuma; 08-26-10 at 20:38. Reason: Add "For example: If I designed, ..."

  7. #7
    Join Date
    Aug 2010
    Posts
    21
    Hi .this is my first project and i beginner in Data Base ,and i must use constant for design Data base. I inserted all data manually with command insert .

Posting Permissions

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