Tri Setia

Tri Setia

  • 1.3k
  • 464
  • 24.7k

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

Apr 19 2021 4:14 AM
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
  1. select * from barang  
  2. select * from pembelian  
  3.   
  4. insert into barang (namabarang,stockbarang) Values ('Aqua','10')  
  5.   
  6. insert into pembelian (idpembelian,namabarang,jumlahbeli) Values ('1','Aqua','10')  
  7.   
  8. Update pembelian Set namabarang='Aqua',jumlahbeli='5' Where idpembelian='1'  
  9.   
  10. --the correct result is 15 not 25 if this  
  11. --statement Update pembelian Set namabarang='Aqua',jumlahbeli='5' Where idpembelian='1' execute  
this is trigger for added stockbarang in table barang
  1. Create trigger [dbo].[tambahstockbarang] on [dbo].[pembelian]  
  2. for insert  
  3. as  
  4. update b set b.stockbarang=b.stockbarang+i.jumlahbeli  
  5. from barang b join inserted i on b.namabarang=i.namabarang   
this is trigger for update stockbarang in table barang
  1. Create trigger [dbo].[ubahstockbarangpembelian] on [dbo].[pembelian]  
  2. after insertupdate  
  3. as  
  4. update barang set barang.stockbarang=(barang.stockbarang+deleted.jumlahbeli)-inserted.jumlahbeli from inserted,deleted  
  5. where deleted.namabarang=inserted.namabarang  

please help correct my trigger for update stockbarang.

Any help could be appriciate 


Answers (3)