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