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
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 trigger not working

Author  Topic 

taniarto
Starting Member

27 Posts

Posted - 2013-02-27 : 03:30:20
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

52326 Posts

Posted - 2013-02-27 : 04:06:32
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

27 Posts

Posted - 2013-02-27 : 22:54:16
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)

17689 Posts

Posted - 2013-02-27 : 23:04:12
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

taniarto
Starting Member

27 Posts

Posted - 2013-03-11 : 03:19:45
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
[spoiler]Time is always against us[/spoiler]



Go to Top of Page

taniarto
Starting Member

27 Posts

Posted - 2013-03-11 : 04:50:30
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
[spoiler]Time is always against us[/spoiler]





Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 06:33:34
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
   

- Advertisement -