How to update data barang on table barang using trigger in sql server

Tri Setia

Hi guys,, I'm get trouble how to update value using trigger on table barang when data after update from table pembelian. For example first stock in table barang is 10, and then in table pembelian buy jumlahbeli 10, stock in table barang increased become 20. My question is how to update stock in table barang if in table pembelian existing data updated.

this is the xample in insert and update

select * from barang
select * from pembelian
insert into barang (namabarang,stockbarang) Values ('Aqua','10')
insert into pembelian (idpembelian,namabarang,jumlahbeli) Values ('1','Aqua','10')
Update pembelian Set namabarang='Aqua',jumlahbeli='5' Where idpembelian='1'
--the correct result is 15 not 25 if this
--statement Update pembelian Set namabarang='Aqua',jumlahbeli='5' Where idpembelian='1' execute

this is trigger for added stockbarang in table barang

Create trigger [dbo].[tambahstockbarang] on [dbo].[pembelian]
for insert
as
update b set b.stockbarang=b.stockbarang+i.jumlahbeli
from barang b join inserted i on b.namabarang=i.namabarang

this is trigger for update stockbarang in table barang

Create trigger [dbo].[ubahstockbarangpembelian] on [dbo].[pembelian]
after insert, update
as
update barang set barang.stockbarang=(barang.stockbarang+deleted.jumlahbeli)-inserted.jumlahbeli from inserted,deleted
where deleted.namabarang=inserted.namabarang

please help correct my trigger for update stockbarang.

Any help could be appriciate

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tri Setia5y ago
alhamdulilah
problem solved,,
use this trigger working properly..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE trigger [dbo].[ubahstockbarangpembelian] on [dbo].[pembelian]
after update,delete,insert
as
update barang set barang.stockbarang = (barang.stockbarang - deleted.jumlahbeli) + inserted.jumlahbeli
from deleted,inserted
where barang.namabarang=deleted.namabarang
0
Tri Setia5y ago
@Mario Scheeren
yes thats right what you say sir,,
the trigger fired twice, I'm try to solve it but not yet get the solution.
maybe you can help me sir for correct that trigger above especially trigger for update stockbarang in table barang.
0
Mario Scheeren5y ago
I guess the second Trigger fires twice.

insert into pembelian => tambahstockbarang fired, ubahstockbarangpembelian fired

=> stockbarang = 10 + 10 - 0 + 10 = 30

update pembelian => ubahstockbarangpembelian fired

=> stockbarang = 30 - 10 + 5 = 25

So the behavior is quiet obvious and correct.

ubahstockbarangpembelianonly needs to fire at: update NOT after insert

You need to understand when a trigger fires ... and when those "combinated" words take an effect
+1