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

You already posted some threads about same problem.

Update

trigger

Trigger Error

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

i answered to your question

and now completely show my question

I think you didn't answer to my question in thread Update

I have some other questions to your answer.

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.

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 .