trigger not working - 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

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/

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/

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]

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]

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]

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources