Versione te komandes e sakteBEGIN TRAN <emertransaksion> ........... ........... ........... COMMIT TRAN <emertransaksion> --- e sakte BEGIN TRAN ........... ........... ........... COMMIT TRAN --- e sakte BEGIN TRANSACTION ........... ........... ........... COMMIT TRANSACTION
Kontrolli i transaksionit
-- si e kontrollojme qe transaksioni eshte i sakte -- perdorim ROLLBACK TRANSACTION <emer> ROLLBACK TRAN <emer> ROLLBACK TRANSACTION ROLLBACK TRAN
Si te gjejme ne ka gabim?
variabli @@ERROR
eshte i barabarte me 0 kur nuk ka patur gabim
eshte i ndryshem nga 0 kur ka ndodhur nje gabim
SHEMBULL
TE BEJME NJE TRANSAKSION QE BEN DALJE PRODUKTE
-- te bejme dalje produktet qe kane 3 njesi gjendje
-- te bejem dalje produktet qe kane 0 njesi gjendje
-- shembulli 1 pa transaksion
update Products set UnitsInStock=UnitsInStock-1 where UnitsInStock=0
update Products set UnitsInStock=UnitsInStock-1 where UnitsInStock=3
E para dha gabim , bie ndesh me kufizimin (UnitsInstock>0)
e dyta shkoi me sukses
-- shembull 2 me transaksion
BEGIN TRANSACTION Dalje update Products set UnitsInStock=UnitsInStock-1 where UnitsInStock=2
update Products set UnitsInStock=UnitsInStock-1 where UnitsInStock=0 IF (@@ERROR<>0) BEGIN ROLLBACK TRANSACTION Dalje print 'Transaksioni i daljes deshtoi' END ELSE BEGIN COMMIT TRANSACTION Dalje print 'Transaksini shkoi me sukses' END
-- i gjithe transaksioni eshte kthyer mbrapsht
-- shembull 3 me transaksion, gabimi ne fillim
BEGIN TRANSACTION Dalje
update Products
set
UnitsInStock=UnitsInStock-1
where UnitsInStock=0
update Products
set
UnitsInStock=UnitsInStock-1
where UnitsInStock=2
IF (@@ERROR<>0)
BEGIN
ROLLBACK TRANSACTION Dalje
print 'Transaksioni i daljes deshtoi'
END
ELSE
BEGIN
COMMIT TRANSACTION Dalje
print 'Transaksini shkoi me sukses'
END
-- transaksioni shkoi me sukses
--????????????????????????????
-- @@ERROR ruan statusin e gabimit te transaksionit
-- me te fundit
-- veprimi i fundit pa gabim zeron @@error
Kontroll me i mire i transaksionit -- kontroll me mire behet -- me TRY CATCH
--Sintaksa
BEGIN TRY .... RRESHTA KODI .... RRESHTA KODI .... RRESHTA KODI .... RRESHTA KODI .... RRESHTA KODI END TRY BEGIN CATCH .... RESHTA KODI QE EKEZEKUTOHEN ....VETEM NESE KA GABIM END CATCH
-- KONTROLLI I TRANSAKSIONIT -- E VENDOSIM TRANSAKIONIN BRENDA BLLOKUR TRY -- VENDOSIM KODIN QE TRAJTON --GABIMET TEK BLLOKU CATCH BEGIN TRY BEGIN TRANSACTION Dalje update Products set UnitsInStock=UnitsInStock-1 where UnitsInStock=0
update Products set UnitsInStock=UnitsInStock-1 where UnitsInStock=1 COMMIT TRANSACTION Dalje PRINT 'Transaksioni shkoi me sukses' END TRY BEGIN CATCH ROLLBACK TRANSACTION Dalje print 'Transaksioni deshtoi me sukses' END CATCH
USHTRIME
Ushtrim 1
Ndertoni nje transaksion i cili :
1) te gjithe punonjesit qe kane realizuar me pak se 50 porosi i ben me Title='Pushuar nga puna'
2) Fshin te gjithe porosite qe ata kane realizuar
Zgjidhje
BEGIN TRY BEGIN TRAN UPDATE Employees SET Title='Pushuar nga puna' where EmployeeID IN ( select EmployeeID from Orders group by EmployeeID having COUNT(*)<50 )
DELETE Orders WHERE EmployeeID IN ( select EmployeeID from Orders group by EmployeeID having COUNT(*)<50 ) COMMIT TRAN print 'transaksioni shkoi me sukses' END TRY BEGIN CATCH ROLLBACK TRAN Print 'transaksioni deshtoi' END CATCH
Ushtrim 2
Ndertoni nje transaksion qe shton nje porosi per produktin me kodin 5, per klientin me kodin ALFKI dhe punonjesin me kodin 1.
Sasia e porositur do te jete 10 ndersa cmimi i barabarte me cmimin e produktit
Nese nuk ka gjendje transaksioni duhet te kthehet mbrapsht
begin try BEGIN TRANSACTION Declare @oid INT Declare @price money select @price=UnitPrice from products where ProductID=5 print 'Price of product 5 is: '+convert(varchar,@price); insert into orders(EmployeeID,CustomerID) values (1,'ALFKI'); set @oid=@@IDENTITY print 'A new order is added with orderid: '+convert(varchar,@oid);
insert into [Order Details](OrderID,ProductID,UnitPrice,Quantity) values (@oid,5,@price,10);
print 'An order for product 5 is entered.'; print 'Updating product stock';
update products set UnitsInStock=UnitsInStock-10 where productId=5;
print 'Product Stock is updated';
COMMIT TRANSACTION end try begin catch ROLLBACK TRANSACTION PRINT 'Transaksioni deshtoi' end catch