Update table data using Trigger in SQL Server

trisetia302
 
on Apr 18, 2021 10:58 PM
736 Views

Hi guys,

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

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

Download FREE API for Word, Excel and PDF in ASP.Net: Download
trisetia302
 
on Apr 20, 2021 08:20 AM
alhamdulilah
problem solved,,
use this trigger working properly sir.. 
 
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