TRANSACTION
1. Apa itu transaction?
Sebuah transaksi adalah sekelompok perintah yang harus dijalankan, hasilnya berhasil atau gagal untuk semua perintah tersebut
2. Apa pentingnya transaction?
Lihat contoh berikut : missal diperlukan perubahan stok setiap kali ada penjualan 1 item barang
Update Inventory
Set qty = qty – 1
Where item_id=2012
Insert Sales_detail (item_id, inv_id, datesold, qty)
Values(2012, 308, current_timestamp, 1)
BEGIN TRAN
Update Inventory
Set qty = qty – 1
Where item_id=2012
Insert Sales_detail (item_id, inv_id, datesold, qty)
Values(2012, 308, current_timestamp, 1)
COMMIT TRAN
LATIHAN 1:
--LIHAT DATA SEBELUM DIUPDATE
select * from FB
BEGIN TRAN
--LAKUKAN UPDATE
update FB
set tempo=200 where KODEPELANGGAN='S-00002'
update FB
set tempo=300 where KODEPELANGGAN='S-00003'
--LIHAT DATA SESUDAH DIUPDATE
select * from FB
--LAKUKAN ROLLBACK
ROLLBACK TRAN
--LIHAT DATA SESUDAH ROLLBACK
select * from FB
HASILNYA :
LATIHAN 2 :
--LIHAT DATA SEBELUM DIUPDATE
select * from FB
BEGIN TRAN
--LAKUKAN UPDATE
update FB
set tempo=200 where KODEPELANGGAN='S-00002'
update FB
set tempo=300 where KODEPELANGGAN='S-00003'
--LIHAT DATA SESUDAH DIUPDATE
select * from FB
--LAKUKAN COMMIT
COMMIT
--LIHAT DATA SESUDAH COMMIT
select * from FB
HASILNYA :
LATIHAN 3 :
select * from fb
BEGIN TRAN
insert into fb(NoFJ,KodePelanggan,tglFJ,totalfaktur)
VALUES ('FB-3','S-00002','2007-11-06 12:12:12','10000')
update Supplier
set Piutang = Piutang+10000 where KODEPELANGGAN ='S-00002'
select * from fb
select * from supplier
COMMIT
HASILNYA
1. Apa yang dihasilkan jika perintah ini dijalankan
-- Create table to work with
CREATE TABLE MyTranTest
(
OrderID INT PRIMARY KEY IDENTITY
)
select * from mytrantest ---------------------(A)
BEGIN TRAN TranStart
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(B)
SAVE TRAN FirstPoint
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(C)
ROLLBACK TRAN FirstPoint
select * from mytrantest ---------------------(D)
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(E)
SAVE TRAN SecondPoint
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(F)
ROLLBACK TRAN SecondPoint
select * from mytrantest
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(G)
COMMIT TRAN TranStart
SELECT TOP 3 OrderID
FROM MyTranTest
ORDER BY OrderID ASC ---------------------(H)
No comments:
Post a Comment