| Author |
 Topic  |
|
|
taniarto
Starting Member
Indonesia
9 Posts |
 Posted - 02/27/2013 : 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
India
47173 Posts |
Posted - 02/27/2013 : 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
Indonesia
9 Posts |
Posted - 02/27/2013 : 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)
Singapore
16746 Posts |
Posted - 02/27/2013 : 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
Time is always against us
|
|
|
|
taniarto
Starting Member
Indonesia
9 Posts |
Posted - 03/11/2013 : 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
Time is always against us
|
|
|
|
taniarto
Starting Member
Indonesia
9 Posts |
Posted - 03/11/2013 : 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
Time is always against us
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 03/11/2013 : 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/
|
|
|
| |
Topic |
|
trigger not working
