SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 trigger not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

taniarto
Starting Member

Indonesia
27 Posts

Posted - 02/27/2013 :  03:30:20  Show Profile  Reply with Quote
I Have tables as below :
1.Purchase
field :
id varchar(10)
item_id varchar(10)
qty smallint

2. Stock
field :
item_id varchar(10)
qty smallint

I create a trigger :
alter TRIGGER [insert_purch] ON [dbo].[purchase]
FOR INSERT
AS
Declare @item varchar(10)
select @item_id = item_id from inserted
update stock set stock.qty =stock.qty+purchase.qty from stock,purchase where stock.item_id=@item_id

The problem is the trigger not run, but when I change for insert into for insert,update it's working but the stock qty were added multiply

Please help...

thanks

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/27/2013 :  04:06:32  Show Profile  Reply with Quote
it should be


alter TRIGGER [insert_purch] ON [dbo].[purchase] 
FOR INSERT
AS

update s
set s.qty = s.qty + p.qty 
from stock s
join (select item_id,sum(qty) as qty
      from inserted 
      group by item_id)i
on s.item_id = i.item_id


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

Go to Top of Page

taniarto
Starting Member

Indonesia
27 Posts

Posted - 02/27/2013 :  22:54:16  Show Profile  Reply with Quote
what is the meaning of initial 'i' ?

thanks
quote:
Originally posted by visakh16

it should be


alter TRIGGER [insert_purch] ON [dbo].[purchase] 
FOR INSERT
AS

update s
set s.qty = s.qty + p.qty 
from stock s
join (select item_id,sum(qty) as qty
      from inserted 
      group by item_id)i
on s.item_id = i.item_id


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



Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 02/27/2013 :  23:04:12  Show Profile  Reply with Quote
i is the alias for the derived table

update s
set s.qty = s.qty + i.qty -- should be i over here
from stock s
join (select item_id,sum(qty) as qty
      from inserted 
      group by item_id)i
on s.item_id = i.item_id



KH
Time is always against us

Go to Top of Page

taniarto
Starting Member

Indonesia
27 Posts

Posted - 03/11/2013 :  03:19:45  Show Profile  Reply with Quote
Sorry mr.khtan for not understand ..
please explain to me :
is right :
s is reference to stock table
i is reference to purchase table ?
why there's no information like stock as s..

thanks

quote:
Originally posted by khtan

i is the alias for the derived table

update s
set s.qty = s.qty + i.qty -- should be i over here
from stock s
join (select item_id,sum(qty) as qty
      from inserted 
      group by item_id)i
on s.item_id = i.item_id



KH
Time is always against us



Go to Top of Page

taniarto
Starting Member

Indonesia
27 Posts

Posted - 03/11/2013 :  04:50:30  Show Profile  Reply with Quote
dear mr khtan,
the trigger is working but if there were 2 record the update only running at the second record it cant work for the first record..

thanks



quote:
Originally posted by taniarto

Sorry mr.khtan for not understand ..
please explain to me :
is right :
s is reference to stock table
i is reference to purchase table ?
why there's no information like stock as s..

thanks

quote:
Originally posted by khtan

i is the alias for the derived table

update s
set s.qty = s.qty + i.qty -- should be i over here
from stock s
join (select item_id,sum(qty) as qty
      from inserted 
      group by item_id)i
on s.item_id = i.item_id



KH
Time is always against us





Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/11/2013 :  06:33:34  Show Profile  Reply with Quote
quote:
Originally posted by taniarto

dear mr khtan,
the trigger is working but if there were 2 record the update only running at the second record it cant work for the first record..

thanks





The trigger will be called only once and will process both the inserted records in a batch

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000