Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger

Author

Topic

calamaris
Starting Member

2 Posts

Posted - 2010-08-26 : 10:40:24

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 10:58:06

are you using db2? dont know if this will work,but this is how you do it in ms sql


create trigger dec_num
 on sales
for insert
as
begin
UPDATE  p
SET p.pro_qty = p.pro_qty - 1
FROM product p
JOIN INSERTED i
ON i.pro_id = p.product_id
end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

calamaris
Starting Member

2 Posts

Posted - 2010-08-26 : 11:16:21

yes i use IBM DB2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 11:23:37

quote:
Originally posted by calamaris

yes i use IBM DB2

then you might get more accurate solution if you post this in some DB2 forums like www.dbforums.com
This is MS SQL Server forum and solutions given here are SQL Server specific. you may try this though and see if it works

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources